howto backup PostgreSQL databases

FreeBSD Installation tips

howto backup PostgreSQL databases

Postby stephen » Tue Jul 13, 2010 5:32 pm

Backing up PostgreSQL databases
Postgresql pg_dump manual page
http://www.postgresql.org/docs/8.4/static/app-pgdump.html
Here is my script t backup
1. create a backup directory mkdir pgsql_backup
2 give the pgsql user ownership
chown pgsql pgsql_backup
3 change user to pgsql user
su pgsql
4. run the backup
pg_dump postgres > pgsql_backup/postgres_26aug10.sql



To create the above in a cron job the password needs to be stored see following quotes
http://www.issociate.de/board/post/43225/pg_dump_+_cronjob.html
export PGPASSWORD=mypassword



The file .pgpass in a user's home directory is a file that can contain
passwords to be used if the connection requires a password (and no
password has been specified otherwise). This file should have lines of
the following format:
hostname:port:database:username:password

Each of the first four fields may be a literal value, or *, which
matches anything. The password field from the first line that matches
the current connection parameters will be used. (Therefore, put
more-specific entries first when you are using wildcards.) If an entry
needs to contain : or \, escape this character with \.


http://www.issociate.de/board/post/195308/PostgreSQL_Backup_Script.html

http://www.postgresql.org/docs/8.1/static/backup.html
PostgreSQL provides the utility program pg_dump for this purpose. The basic usage of this command is:

pg_dump dbname > outfile

23.1.1. Restoring the dump

The text files created by pg_dump are intended to be read in by the psql program. The general command form to restore a dump is

psql dbname < infile

where infile is what you used as outfile for the pg_dump command. The database dbname will not be created by this command, you must create it yourself from template0 before executing psql (e.g., with createdb -T template0 dbname). psql supports options similar to pg_dump for controlling the database server location and the user name. See psql's reference page for more information.
stephen
 
Posts: 507
Joined: Thu Feb 09, 2006 9:37 am
Location: Brisbane

Return to FreeBSD

Who is online

Users browsing this forum: No registered users and 1 guest

cron