Garbage collection and backups in PostgreSQL
Author: Nediam <javier@nediam.com.mx>
Publication date: 2005-09-16
Last updated: 2006-05-04
Note: The examples shown here were tested on PostgreSQL 8.0.2.
DATABASE GARBAGE COLLECTION:
* Note: pg_username refers to the PostgreSQL username and not to the system username.
- To clean up all databases of a database cluster:
user@SERVER:~$ vacuumdb -h 127.0.0.1 -f -a -v -z -U pg_username - To clean up a single database of a database cluster:
user@SERVER:~$ vacuumdb -h 127.0.0.1 -f -v -z -U pg_username database_name - To clean up a single table of a database:
user@SERVER:~$ vacuumdb -h 127.0.0.1 -f -v -z -U pg_username -t table_name database_name
DATABASE BACKUPS:
* Note: pg_username refers to the PostgreSQL username and to the system username.
- To backup all databases of a database cluster:
user@SERVER:~$ pg_dumpall -h 127.0.0.1 -o > file1 - To restor all databases of a database cluster:
user@SERVER:~$ psql -h 127.0.0.1 -f file1 template1
- To backup a single database of a database cluster:
user@SERVER:~$ pg_dump -h 127.0.0.1 -o -v -f file2 -U pg_username database_name - To restore a single database of a database cluster:
(Note: The database where the information will be restored must be previously created)
user@SERVER:~$ psql -h 127.0.0.1 -f file2 database_name pg_username
- To backup a single table of a database:
user@SERVER:~$ pg_dump -h 127.0.0.1 -o -v -f file3 -U pg_username -t table_name database_name - To restore a single table of a database:
(Note: The database where the information will be restored must be previously created)
user@SERVER:~$ psql -h 127.0.0.1 -f file3 database_name pg_username
- To backup a database schema:
user@SERVER:~$ pg_dump -h 127.0.0.1 -o -v -f file4 -U pg_username -n schema_name database_name - To restore a database schema:
(Note: The database where the information will be restored must be previously created)
user@SERVER:~$ psql -h 127.0.0.1 -f file4 database_name pg_username
- To backup a table of a particular database schema:
user@SERVER:~$ pg_dump -h 127.0.0.1 -o -v -f file5 -U pg_username -t table_name -n schema_name database_name - To restore a table in a particular database schema:
(Note: The database where the information will be restored must be previously created and so does the schema. If the target schema doesn't exist, the information will be restored inside the public schema)
user@SERVER:~$ psql -h 127.0.0.1 -f file5 database_name pg_username
For more information, check the PostgreSQL documentation:
http://www.postgresql.org/docs/8.1/static/app-vacuumdb.html
http://www.postgresql.org/docs/8.1/static/backup.html
The latest version of this document is available at:
http://nediam.com.mx/en/tips/backups_pgsql.php