Best pg_dump practices

From: MT <m_tessier(at)sympatico(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: Best pg_dump practices
Date: 2003-06-12 16:58:12
Message-ID: 20030612125812.515e6402.m_tessier@sympatico.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

Now that I've painstakinly entered my data into the db, I'd like to perform regular backups using pg_dump as such

pg_dump -c -f dumpfile.sql dbname

This will give me the data in its original, pristine form. Note that using pg_dump this way means that the data gets dumped as copy too. Is there a way to dump only the db objects (ie. tables, sequences, etc) and exclude the data.

Then, as the db is used, I would perform daily backups, automated with cron.

pg_dump -a -f daily_dumpfile.sql dbname

I would then tar and gzip the daily_dumpfile.sql and upload it to a backup server.

Now, if the database should suddenly crash, I would do retrieve the dumpfiles, untar them and

psql dbname
\i dumpfile.sql
This would create the db in its original form.

\i daily_dumpfile.sql
This would bring the reconstituted db up to date.

Then again, »I'm not sure if this works. Furthermore, maybe someone could recommend a better way to perform this task.

--
Thanks,

Mark

Browse pgsql-general by date

  From Date Subject
Next Message Dennis Gearon 2003-06-12 16:58:49 Re: [HACKERS] SAP and MySQL ... [and Benchmark]
Previous Message Rory Campbell-Lange 2003-06-12 16:57:50 Re: Cast: timestamp to integer