Centos – why postgresql doesn’t initdb at startup

centospostgresql

I observed a significant change in /etc/init.d/postgresql is changed in new version (postgresql-8.4.7) I was using postgresql-8.1.23 and /etc/init.d/postgresql start would do initdb if cluster doesn't exits which is not the case now. I have to do initdb exclusively.

Do you know why this is changed like this?
Do you know from which version it is like this? (I checked in the release notes, but couldn't find it)

Probem I am facing is I have a kickstart install of CentOS which includes my packges depedning on postgres. So, I do /sbin/chkconfig postgresql on in the %post. Now, since initdb is not done by default, I am not able to start my service at startup.

Best Answer

This change was made because it was surprising, unwanted behavior for people who ultimately intended to put the database into another location besides the default one--an extremely common change. The fact that older versions bundled the initdb step into startup was a problem for them. The newer versions avoid this by making it an explicit step. It's still easy, it's just not automatic in a way that can do things wrong for people customizing the destination. I'm not sure exactly which version it was where the packagers made this change, but it was either 8.2 or 8.3. Been there long enough that it's unlikely to revert to the original behavior now.

If you want to automate this in a kickstart fashion, you need a script that sets things up in the case where they're not right yet. Here's the sort of code you need to run on boot:

PGDATA=/var/lib/pgsql/data
. /etc/sysconfig/pgsql/postgresql
if [ ! -f "$PGDATA/PG_VERSION" ] ; then
  /sbin/service postgresql initdb
  /sbin/service postgresql start
fi

You could put that into its own init script, or you might just customize /etc/rc.d/rc.local and include it there. Written like that, it will be a quick harmless check each time. Might be possible to eliminate it completely after running once by using something like the firstboot init script.

Related Solutions

Postgresql – How to create a PostgreSQL web application user with limited privileges as easy as possible

Mass GRANT/REVOKE is only available on PostgreSQL 9.0+.

Instead, you can create the functions pg_grant() and pg_revoke() found at http://blog.bhushangahire.net/2009/06/17/grant-privileges-to-all-tables-in-a-database-for-postgresql/

The parameters to both functions are:

User Role
Permissions
Object name, may be a pattern. "%" affects all objects
Schema name

You will need to run the command on each database and each schema. The syntax looks like this:

SELECT pg_grant('web_user','SELECT,INSERT,UPDATE,DELETE','%','public');

If you like, you may be able to change this line in the function definition:

nspname = $4 AND

to this:

nspname like $4 AND

in order to be able to specify schema as a wildcard, but I haven't tested it.

Ubuntu – I can’t get Postgres configured correctly

Then I installed and ran chkconfig --add postgresql

You need also turn on it:

chkconfig postgresql on

Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

Did you start the postgresql?

$ ps -ef | grep postgre

What is that domain socket directory, and do I need to change it?

It contains the Unix domain socket which server is to listen for connections from the clients. You don't need to change (it can be done with unix_socket_directory).

Now, the problem is that, I still need to write out the full location of the directory when running a command. Hmm, how do I fix this.

Add it to the $PATH environment variable by inserting/appending the belows line to ~/.profile in your $HOME:

export PATH=$PATH:/usr/local/pgsql/bin

Make this take effect with:

$ source ~/.profile

or:

$ . ~/.profile

You can also add to /etc/profile for global usage.

Best Answer

Related Solutions

Postgresql – How to create a PostgreSQL web application user with limited privileges as easy as possible

Ubuntu – I can’t get Postgres configured correctly

Related Topic