postgresql dump and load -- need to dump "postgres" db also?

Question

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

Answer 1

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.

• Rob Bednark: Thanks @a_horse_with_no_name! I have some roles that need to get copied as well, so I used "pg_dumpall --roles-only" to get them. I assumed they would be stored in the "postgres" db and that I could just pg_dump "postgres" db, but the roles were not there.