PG_restore help

Question

Good day all,

I am fairly new with PQSQL and am slowly picking things up - I have added a new disk and would like to do two things:

1. Restore a backup to this new disk - /hda2/pgdata/
2. Move a database from /hda1/pgdata to /hda2/pgdata/

Any help is most appreciated, thank you.

Answer 1

Q1. Use pg_restore to restore a database. Check out the documentation which is very clear.

One important thing to remember, if you want to move to a later version of PostgreSQL use the later version of pg_dump to create a backup dump file. For example if you want to move from PostgreSQL version 8.3 to version 8.4, then create a backup dump file using pg_dump from version 8.4 and then use pg_restore 8.4 to recreate database in the 8.4 server.

http://www.postgresql.org/docs/8.4/static/app-pgrestore.html

Q2. Backup and restore is a safe way of doing it. Before restoring one can create a tablespace on the new disk and place the database in that space.

CREATE DATABASE mydb TABLESPACE myspace;

http://www.postgresql.org/docs/8.4/interactive/manage-ag-tablespaces.html