Postgresql – Drop and restore PostgresSQL database without losing settings

My understanding of pg_restore (which you'll have to use because you created a pg_dump with format=custom) is that if you supply the --dbname=... flag you will restore into that database (without dropping it).

If you supply the --clean command the db and objects will be dropped before being recreated.

If no --dbname=... flag is supplied, the creation commands will spool to stdout, which you can redirect to a file and load using psql.

To test, try:

pg_dump --format=c --no-privileges --no-owner --file=/tmp/`hostname`-`date +%d%m%Y`.backup --username=admin --host=127.0.0.1 database_name

pg_restore your_dump_file > createdb.sql

psql -d database_name -U postgres -f createdb.sql

You are not doing anything wrong per se, and the documentation is not wrong. But the initscript from CentOS doesn't support you doing things this way, so you have to change the initscripts to not check for these files, since PostgreSQL will check for it itself and just try to start the database even if these files are not there.

So to test it out, comment out the if, then and the whole else block and just let it run the ... part of the script. PostgreSQL should read the settings correctly and start itself without a problem. And you might of course inform the people responsible for the CentOS package that they should do it differently. If it doesn't run, you should get a reasonable error message from PostgreSQL itself.