You can dump the whole PostgreSQL cluster with pg_dumpall. That's all the databases and all the globals for a single cluster. From the command line on the server, I'd do something like this. (Mine's listening on port 5433, not on the default port.) You may or may not need the --clean option.
$ pg_dumpall -U postgres -h localhost -p 5433 --clean --file=dump.sql
This includes the globals--information about users and groups, tablespaces, and so on.
If I were going to backup a single database and move it to a scratch server, I'd dump the database with pg_dump, and dump the globals with either
pg_dumpall --globals-only, orpg_dumpall --roles-only (if you only need roles)
like this.
$ pg_dump -U postgres -h localhost -p 5433 --clean --file=sandbox.sql sandbox
$ pg_dumpall -U postgres -h localhost -p 5433 --clean --globals-only --file=globals.sql
Outputs are just text files.
After you move these files to a different server, load the globals first, then the database dump.
$ psql -U postgres -h localhost -p 5433 < globals.sql
$ psql -U postgres -h localhost -p 5433 < sandbox.sql
I thought pg_dumpall would at least backup foreign keys, but even that
seems to be an 'option'. According to:
http://www.postgresql.org/docs/9.1/static/app-pg-dumpall.html even
with pg_dumpall I need to use a -o option to backup foreign keys
No, that reference says "Use this option if your application references the OID columns in some way (e.g., in a foreign key constraint). Otherwise, this option should not be used." (Emphasis added.) I think it's unlikely that your application references the OID columns. You don't need to use this option to "backup foreign keys". (Read the dump file in your editor or file viewer.)
It's failing when trying to run the old bin files because it's missing a dependency. You're going to need to install its dependencies, install 8.4 on the new server for this task, or spin up a VM with 8.4 installed, copy the files to the VM, do what it takes to start the 8.4 instance on the VM (which means that postgres will need to know where the default data directory is). From there, I'd do a pg_dump on the database(s) in question and then restore them to the new server. That would probably give you the cleanest environment.
So, here are what I see as your options:
Using pg_upgrade
On The New Server (pg_upgrade)
- Install PostgreSQL 8.4 dependencies and hope that your copied binaries work. If they don't, then you may need to just remove these copied binaries and install 8.4 from apt.
- Attempt to start the service
/tmp/postgres-bin.old/postgresql/8.4/bin/pg_ctl start -D /tmp/postgres.old/main/
- If that works, stop the service
/tmp/postgres-bin.old/postgresql/8.4/bin/pg_ctl stop -D /tmp/postgres.old/main/
- Now try your pg_upgrade script
Without Using pg_upgrade
- Create a VM or use an old, decommed server and install postgresql 8.4
- Initialize your server using
initdb (or pg_createcluster)
- Copy your backed up data over that which was just created
- Start postgresql
- Backup the data using
pg_dump
- Copy the dump file to the "new server"
- Restore to a new database on the new server
Best Answer
You can select which schemas to dump with the
-noption of pg_dump. Create a dump of schema B:Restore the dump file:
The target database does not have to have the same name as the original one.
Note that you will have problems if schema B has dependencies on schema C. Then you won't be able to restore it separately.