Bug 771496

Summary: On initial install, postgresql-server does not initialize /var/lib/pgsql/data
Product: [Fedora] Fedora Reporter: Matt Rose <mrose>
Component: postgresqlAssignee: Tom Lane <tgl>
Status: CLOSED ERRATA QA Contact: Fedora Extras Quality Assurance <extras-qa>
Severity: unspecified Docs Contact:
Priority: unspecified    
Version: rawhideCC: collura, devrim, hhorak, paulo.fidalgo.pt, tgl
Target Milestone: ---Keywords: Reopened
Target Release: ---   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: postgresql-9.1.2-2.fc16 Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2012-01-19 01:37:00 UTC Type: ---
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:
Attachments:
Description Flags
patch to postgresql.spec none

Description Matt Rose 2012-01-03 21:56:39 UTC
Created attachment 550544 [details]
patch to postgresql.spec

Description of problem:  After initial install of a postgresql server rpm, you are left with an unstartable service until you run the following command as root

su - postgres -c "initdb -D /var/lib/pgsql/data"

Version-Release number of selected component (if applicable):  

[root@oscar]# rpm -q postgresql-server
postgresql-server-9.1.2-1.fc16.x86_64

How reproducible:  Always


Steps to Reproduce:
1.  install postgresql-server
2.  issue "service postgresql start"
3.  
  
Actual results:  postgresql doesn't start

[root@oscar]# service postgresql start
Redirecting to /bin/systemctl  start postgresql.service
Job failed. See system logs and 'systemctl status' for details.

[root@oscar system]# service postgresql status
Redirecting to /bin/systemctl  status postgresql.service
postgresql.service - PostgreSQL database server
	  Loaded: loaded (/lib/systemd/system/postgresql.service; disabled)
	  Active: failed since Tue, 03 Jan 2012 14:46:56 -0500; 3min 12s ago
	 Process: 26290 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=1/FAILURE)
	  CGroup: name=systemd:/system/postgresql.service


Expected results: "service postgresql start to detect whether postgresql has initialized the /var/lib/pgsql/data directory, and, if it hasn't to issue the relevant command to initialize the database.


Additional info:

This definitely falls under the "usability enhancement" category, but as it stands, the postgresql service just fails to start and doesn't give any meaningful information as to what steps to take next.  It makes both Fedora and postgresql look bad.  It seems to be low-hanging fruit.  The one-line change to the spec file is attached

Comment 1 Matt Rose 2012-01-03 21:58:00 UTC
I noticed you use the spec file unchanged from the postgresql PGDG group, should I file a bug with the postgres guys instead?

Comment 2 Tom Lane 2012-01-03 22:57:01 UTC
This is not a bug; it's an intentional design decision recommended by upstream.  We used to do it as you suggest, but stopped after seeing one too many incidents of an auto-initdb overwriting somebody's database.  (An example, not exclusive, of the kind of case where that could happen is when the database is on a remote volume that's just a little bit slow to come online.  We probe, see no database files, start initdb, and then the volume comes online.  Good-bye data.  systemd raises the odds of such race conditions considerably, since it has no good way to wait for all mounts to finish.)

You should run "postgresql-setup initdb" as the postgres user to initialize a database; or in pre-systemd packages you can use "service postgresql initdb".  The pre-systemd scripts used to tell you to do that when necessary, but systemd makes such user-friendliness nigh impossible :-(

BTW, Fedora most certainly does not "use the PGDG specfile unchanged".  There is common ancestry but they diverged years ago.

Comment 3 Tom Lane 2012-01-03 23:06:51 UTC
BTW, looking more closely at your proposed patch, I see you're suggesting running initdb during package install rather than during service start.  That's an interesting idea, but I fear it falls foul of Fedora packaging guidelines.  Packages should not assume that merely getting installed means they'll actually be used.  There's also the issue of someone removing and then reinstalling the server RPM.  We could hope that initdb will always refuse to do anything if there's already a database there, but I'm not convinced that the package install environment is necessarily any safer than the system boot environment for such things.

Comment 4 Matt Rose 2012-01-04 13:43:47 UTC
Thanks for looking at this more.  I don't think that this falls foul of Fedora's packaging guidelines.  It doesn't actually start the service, it just ensures that the service can be started without error when you decide to start it.  I'm sure Fedora makes changes to other services so that they "just work" so to speak.

As you said, this would just run on initial install, so unless the user installed postgresql in /var/lib/pgsql outside of an RPM, and then installed the RPM on top, this would not over-write any data, it just makes sure you have a functional postgresql installation when you install the package.

Please consider re-opening this bug

Comment 5 Matt Rose 2012-01-04 13:53:19 UTC
Oh, and the only reason that I said that it looks like you use the specfile unchanged is that from a cursory reading, it looks very similar to the 9.0 spec from PGDG. 

Again, I hope you consider applying the patch.

Comment 6 Devrim Gündüz 2012-01-04 13:58:34 UTC
(PGDG spec (that I maintain) is *very* different, since PGDG packages have parallel version installation support. )

Anyway, I am with Tom here. Auto-initdb is not what most of the users want. Many prod installations run initdb to a different partition...

Comment 7 Matt Rose 2012-01-04 14:10:52 UTC
With all due respect, without auto-initdb, the package is absolutely useless.  If you're not a postgres DBA, and are installing it as a dependency of another package, and just want a database, the package installation must run initdb.  Yes, for more advanced users they may not want to initialize their db where you want, but they know the commands to do that.  initdb on install just makes sure that the package works for some poor developer who needs to get an installation of postgres up and running.

As Tom said, you can't echo that the user has to run initdb in the init script anymore, so the user feedback component is gone, and the user is left with a package that doesn't work.  running initdb at install time does not harm the advanced user who wants to initialize the db in a different spot, but does make the RPM work as intended.

Comment 8 Devrim Gündüz 2012-01-04 14:53:15 UTC

If the cluster has not been initialized, and when you want to start the database, we ask people to run "service postgresql initdb" to initalize the cluster. Isn't that helpful?

Comment 9 Matt Rose 2012-01-04 15:32:58 UTC
Actually, if you look at the original description, Fedora 16 does not ask people anything.  systemd just fails with a random error message.  "service postgresql initdb" is not in anything communicated to the user on the console, nor in systemctl postgresql.service status

That's the reason I opened this bug in the 1st place.

Comment 10 Matt Rose 2012-01-04 15:34:27 UTC
sorry, I said random when I meant "generic"

Comment 11 Matt Rose 2012-01-04 15:44:23 UTC
for that matter, neither is the "postgresql-setup initdb" command that Tom mentions in Comment #2.  but neither of these commands are obvious to even advanced postgres users.  I've been administering pgsql for years and I didn't even know about them, to be honest.  Your average developer who wants to try postgres out has 0 chance of knowing that they exist, and systemd hides any error message that would give them a clue.

Comment 12 Tom Lane 2012-01-09 17:54:46 UTC
Yeah, the real problem here is that systemd has rendered the setup significantly less user-friendly than it used to be.  I'm not enamored however of Matt's proposal of doing an auto-initdb, partially because that would more than triple the disk footprint of an installed-but-not-used postgresql-server RPM, and partially because it would surprise users who *are* familiar with the package.  I think a more appropriate solution is to try harder to get the systemd scripts to emit a useful error message where the user will see it.

Comment 13 Matt Rose 2012-01-09 18:41:08 UTC
That would be fine, if possible.  From what I've seen, that's not a trivial problem to solve, but if you don't feel you can initialize the database at install time, that would be a good second choice.

Would you like me to open a new bug for that?  I can't give you a patch because I don't have any experience with systemd (unlike postgres and rpm), but I can definitely open a bug.

Comment 14 Tom Lane 2012-01-09 18:56:07 UTC
No need for another bug.

After some investigation I think we can deal with this by adding an ExecStartPre line in the service file to run a shell script that just checks for presence of the database directory and prints an error if not there, similar to what the old initscript did at lines 91...119.  ISTM that I tried to do that when first working with the systemd service, but gave it up because at that time systemd sent output from such scripts to /dev/null.  They've at least fixed that: the output now goes to /var/log/messages.  There's still not any sanctioned way to provide a message directly on the console for a manual "systemctl start" command, though.  So if we do this, you'd see something like

$ sudo systemctl start postgresql.service
Job failed. See system logs and 'systemctl status' for details.

and then if you correctly interpret the reference to "system logs",

$ sudo tail /var/log/messages
Jan  9 13:49:46 rhlap postgresql-check-db-dir[1831]: "/var/lib/pgsql/data" is missing. Use "postgresql-setup initdb" to initialize the cluster first.
Jan  9 13:49:51 rhlap systemd[1]: postgresql.service: control process exited, code=exited status=1

Think that will do?  It's not as nice as I could wish, but short of better support from systemd it's hard to do much better.

Comment 15 Matt Rose 2012-01-09 19:04:26 UTC
That'll at least give 'em a chance.  I say that'll have to do, given the constraints of systemd, and the pg best practices.

Thanks for this.

Comment 16 Tom Lane 2012-01-09 20:48:43 UTC
OK, done.

Comment 17 Fedora Update System 2012-01-09 20:51:35 UTC
postgresql-9.1.2-2.fc16 has been submitted as an update for Fedora 16.
https://admin.fedoraproject.org/updates/postgresql-9.1.2-2.fc16

Comment 18 Fedora Update System 2012-01-11 06:12:32 UTC
Package postgresql-9.1.2-2.fc16:
* should fix your issue,
* was pushed to the Fedora 16 testing repository,
* should be available at your local mirror within two days.
Update it with:
# su -c 'yum update --enablerepo=updates-testing postgresql-9.1.2-2.fc16'
as soon as you are able to.
Please go to the following url:
https://admin.fedoraproject.org/updates/FEDORA-2012-0346/postgresql-9.1.2-2.fc16
then log in and leave karma (feedback).

Comment 19 Matt Rose 2012-01-17 14:10:42 UTC
Looks good to me:

Not wanting to remove my running db, I just changed the systemd configuration to look for the database in /var/lib/pgsql/data2, and when I tried to start postgresql I got the following message in /var/log/messages


Jan 17 09:05:33 oscar postgresql-check-db-dir[1219]: "/var/lib/pgsql/data2" is missing or empty.
Jan 17 09:05:33 oscar postgresql-check-db-dir[1219]: Use "postgresql-setup initdb" to initialize the database cluster.
Jan 17 09:05:33 oscar postgresql-check-db-dir[1219]: See /usr/share/doc/postgresql-9.1.2/README.rpm-dist for more information.

I still think that it should be initialized at install-time, but this will at least give users a fighting chance.

Comment 20 Fedora Update System 2012-01-19 01:37:00 UTC
postgresql-9.1.2-2.fc16 has been pushed to the Fedora 16 stable repository.  If problems still persist, please make note of it in this bug report.

Comment 21 collura 2012-01-22 07:26:39 UTC
0) thanks for error message :')

1) in regard to data integrity and different
installation requirements of 'devs vs dbas vs newbs' user types 
discussed in comments # 2, 7, 19:

  what about adding a separate 'postgresql-auto-initdb' package 
  whose job it would be to set up the auto-initdb?  

that way dba could not to install the auto-initdb package 
while the dev could choose to install the auto-initdb or not as needed
and the newb could try both and eventually figure it out 
when they read the package description :')

an auto-initdb package would not be installed by default , not a depend,
but could be selected as an option to be installed along with postgresql or immediately after after find out that install doesnt run ;')

an auto-initdb package might not be great for security 
or for complicated setups but it would be handy 
for testing and speed adoption for newcomers.

Comment 22 Paulo Fidalgo 2012-02-17 17:35:31 UTC
After installing postgresql and postgresql-server with Apper, I've still have this error:

sudo systemctl status postgresql.service 
postgresql.service - PostgreSQL database server
          Loaded: loaded (/lib/systemd/system/postgresql.service; disabled)
          Active: failed since Fri, 17 Feb 2012 17:15:10 +0000; 57s ago
         Process: 5734 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE)
          CGroup: name=systemd:/system/postgresql.service

from /var/log/messages:

Feb 17 17:32:59 toshiba postgresql-check-db-dir[6153]: "/var/lib/pgsql/data" is missing or empty.
Feb 17 17:32:59 toshiba postgresql-check-db-dir[6153]: Use "postgresql-setup initdb" to initialize the database cluster.
Feb 17 17:32:59 toshiba postgresql-check-db-dir[6153]: See /usr/share/doc/postgresql-9.1.2/README.rpm-dist for more information.
Feb 17 17:32:59 toshiba systemd[1]: postgresql.service: control process exited, code=exited status=1
Feb 17 17:32:59 toshiba systemd[1]: Unit postgresql.service entered failed state.

This is a new fedora installation with all updates applied.

Comment 23 Matt Rose 2012-02-17 17:43:41 UTC
This is actually how this is supposed to work now.  Scroll back up to comments 12,13 and 14 where Tom Lane and I come to a begrudging agreement.