Postgresql dump and load — need to dump “postgres” db also

dumppostgresqlupgrade

If I pg_dump a db from an 8.4 cluster, and then load it into a different 9.1 cluster, do I need to pg_dump and restore the 'postgres' db as well? (I'm not familiar with what is contained in the 'postgres' db)

I want to upgrade a db from 8.4 to 9.1, so I tried pg_upgradecluster but got a lot of errors, so now I'm going to try doing a dump from 8.4 and load the dump into 9.1.

OS: Ubuntu 11.04 Natty

Best Answer

The postgres database is just the default database that is created during initdb. It has no special meaning.

If you created a different one where all your data is kept and you never created tables in the postgres database, then there is no need to dump it.

I don't know what pg_upgradecluster is. The default tool to upgrade the data directory (apart from dump/restore) is pg_upgrade.

If you want help with the upgrade, "got a lot of error" won't get you much help. You need to be a lot more detailed.

Related Solutions

PostgreSQL dump/restore from 8.4 to 8.1 in a shell script

You can drop the table (and dependent DB objects) with the pg_dump parameter --clean, from the pg_dump manual:

-c, --clean Output commands to clean (drop) database objects prior to outputting the commands for creating them. (Restore might generate some harmless errors.)

So in your case this should be

D:\PostgreSQL\8.4\bin\pg_dump -U postgres -p 5433 --clean -t my_table myDB > D:\backup\myFile.sql

you should then be able to restore the dump file with your current restore command in the cron job.

Hope that helps.

Postgresql – pg_restore: [archiver] unsupported version (1.11) in file header postgresql

PostgreSQL really doesn't put much effort into supporting moves from newer major versions back to older ones. One big reason for that is that newer versions usually support features which you just can't install on older versions; although any given database might not be using any of the new features, the project philosophy is that they would rather not support it than to support it in a buggy or incomplete fashion.

Your best bet might be to generate a "plain" format dump rather than a "custom" format dump. This is just a text file of SQL commands. If you aren't using any new features, it might just apply cleanly. If not, you can look into the errors and decide what to do for a back-port.

Best Answer

Related Solutions

PostgreSQL dump/restore from 8.4 to 8.1 in a shell script

Postgresql – pg_restore: [archiver] unsupported version (1.11) in file header postgresql

Related Question