Export content of a DB

From: Ben Clewett <B(dot)Clewett(at)roadrunner(dot)uk(dot)com>
To:
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Export content of a DB
Date: 2003-04-01 08:21:31
Message-ID: 3E894C0B.50509@roadrunner.uk.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice


Is it possible to export an entire DB in a form where it can be
re-imported? For each table in a DB, output to a line-sequential file?

I have found that:

echo "select 'insert into $table values', * from $table |
psql -F " " -A -t $database >> outfile.txt

Does at least give a file of sql statements. But the strings are not
quoted or escaped. So multi-word fields, and field''''s with quotes
mess up.

For this I also need a list of tables in a DB. I can use:

for $table in read `echo "\dt" | psql -F " " -A -t rr | awk '{ print $2 }'`
do
(as above)
done

But it's getting a little weigted! I can't help fealing there must be
some clever option I am missing for dumping a DB in a form designed for
re-import, without all this work!

Thanks in advance if anybody can give me a hand...

Ben

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Crombleholme, Roy 2003-04-01 09:25:34 Re: Export content of a DB
Previous Message Oliver Elphick 2003-03-31 21:24:48 Re: Postgres logs w/ Logrotate