Re: Export content of a DB

>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

THIS reply is long.. stick with it, I find the stuff here exremely useful.

Posgresql comes with its own utility called pg_dump and pg_dumapll. I also
wrote a couple of scripts a while ago to dump databases out of postgresql
using these. Hold on a minute and I'll see if I can find
them........................................................................
............................................ aah, here they are, hope you
find them useful, theres a bit of info with them too. You may have to
compile oid2name, its in the contrib directory where you unpacked the tar
file. When the databases have been dumped you should be able to restore
them using the instructions at the bottom. Enjoy:-

These scripts were just written using vi and uses the bourne shell.

It relies on oid2name that comes with the distribution of postgresql and
you'll obviously need to change the paths so I've just used YOURPATH so you
can modify it easily yourself. I use the first script to get a snapshot of
the database cluster just before a backup of the server is done so that I
know exactly what date and time it was done and so i know that it has not
been changed during the backup time. I call it like this from the cron:
----------------------------------------------------------------------------
-------------
min hr * * * /YOURPATH/dumpdb.sh >> /logs/cron.log 2>&1
----------------------------------------------------------------------------
--------------
#!/bin/sh
#
# Purpose: This script creates a dump of the full database cluster
# into one file and then proceeds to dump individual databases
# into one directory - /PATH/dumpdir
#
# create variables

USER="postgres"
NAMECMD="/YOURPATH/oid2name -U $USER"
TMPDUMP="/YOURPATH/dump.tmp"
DUMPDAT="/YOURPATH/dump.dat"
DUMPDIR="/YOURPATH/dumpdir"
PGPASSWORD="*****" # You'll need to change this
PATH=$PATH:/YOURPATH/bin
export PGPASSWORD PATH

echo "Script $0 starting at `date`"

# Remove any old files

[ -f $TMPDUMP ] && rm -f $TMPDUMP
[ -f $DUMPDAT ] && rm -f $DUMPDAT

# Get database names and put into .tmp file

$NAMECMD | cut -b10- > $TMPDUMP

# Get rid of unwanted stuff in .dat file by filtering out.

while read LineIn; do
CHAR=`echo $LineIn | cut -c1`
if [ "$CHAR" != "" ] && [ "$CHAR" != "-" ]; then
if [ "$LineIn" != "template0" ] && [ "$LineIn" != "template1" ] && [
"$LineIn" != "ases:" ]; then
echo $LineIn >> $DUMPDAT
fi
fi
done<$TMPDUMP

# May as well do a complete dump as well.

[ -f $DUMPDIR/alldata.dmp ] && rm -f $DUMPDIR/alldata.dmp
echo "Commencing full dump into $DUMPDIR/alldata.dmp..."
pg_dumpall -U $USER > $DUMPDIR/alldata.dmp

# Dump individual databases separately so can restore just one if we need.

if [ -f $DUMPDAT ]; then
echo ""
echo "Dumping individual databases..."
while read LineIn; do
echo "dumping $LineIn to $DUMPDIR/$LineIn.dmp"
[ -f $DUMPDIR/$LineIn.dmp ] && rm -f $DUMPDIR/$LineIn.dmp
pg_dump -U postgres -f $DUMPDIR/$LineIn.dmp $LineIn
done<$DUMPDAT
else echo "No individual databases to dump, exiting..."
exit 0
fi

# Tidy up and change owner of files.

chown $USER:$USER $DUMPDIR/*

[ -f $TMPDUMP ] && rm -f $TMPDUMP
[ -f $DUMPDAT ] && rm -f $DUMPDAT

echo "Script completed at `date`"

----------------------------------------------------------------------------
-

This next script would only need to be used if you're databases became
corrupted and you needed to get rid of them all before using the output file
from pg_dumpall to do a full restore. You may find another use. Be careful
with this!!!

----------------------------------------------------------------------------
-
#!/bin/sh
#
# create variables

USER="postgres"
NAMECMD="/YOURPATH/oid2name -U $USER"
TMPDUMP="/YOURPATH/dump.tmp"
DUMPDAT="/YOURPATH/dump.dat"
PGPASSWORD="******"
PATH=$PATH:/YOURPATH/bin
export PGPASSWORD PATH

echo "Script $0 starting at `date`"

# Remove any old files

[ -f $TMPDUMP ] && rm -f $TMPDUMP
[ -f $DUMPDAT ] && rm -f $DUMPDAT

# Confirm to proceed

echo "Are you sure you want to drop all databases? [y/n]: \c"
read ANSWER

if [ $ANSWER != "y" ] && [ $ANSWER != "ye" ] && [ $ANSWER != "yes" ] && [
$ANSWER != "Y" ] && [ $ANSWER != "YE" ] && [ $ANSWER != "YES" ]; then
echo ""
echo "Exiting, no databases have been affected."
exit 0
fi

# Get database names and put into .tmp file

$NAMECMD | cut -b10- > $TMPDUMP

# Get rid of unwanted stuff in .dat file by filtering out.

while read LineIn; do
CH=`echo $LineIn | cut -c1`
if [ "$CH" != "" ] && [ "$CH" != "-" ]; then
if [ "$LineIn" != "template0" ] && [ "$LineIn" !=
"template1" ]
&& [ "$LineIn" != "ases:" ]; then
echo $LineIn >> $DUMPDAT
fi
fi
done<$TMPDUMP

# Drop all the databases

if [ -f $DUMPDAT ]; then
echo ""
echo "Continuing to drop all databases..."
while read LineIn; do
echo "Dropping database - $LineIn..."
dropdb -U postgres $LineIn
done<$DUMPDAT
else
echo ""
echo "No databases exist, exiting..."
exit 0
fi

# Tidy up after myself!

[ -f $TMPDUMP ] && rm -f $TMPDUMP
[ -f $DUMPDAT ] && rm -f $DUMPDAT

echo "Script completed at `date`"

-------------------------------------------------------------------------
To dump a single database:-

pg_dump -U <superuser> -f <outputfile> <databasename>
-------------------------------------------------------------------------
This bit tells you how to restore a single database

-------------------------------------------------------------------------
To restore a single database we first need to know who is the owner of the
database so that the same user can be used to restore the database(I know u
can do it later but its easier like this). We then use the command:

dropdb -U postgres database

This gets rid of the database so we can build it again from scratch.
To be able to restore to this database we need to recreate it using the
command:

createdb -U user database

Now we have an empty database to which we can restore our data. This can
be done as follows:

psql -U user database < database.dump

The database should now be returned to the same state as when the dump was
done
----------------------------------------------------------------------------
-------
THE END!!!! :)

Roy Crombleholme
Trainee ICT Engineer
ICT Services
Resources Directorate
Lancashire County Council
01772 26-1849
01772 5-31849 (wef Easter Monday (21/04/03) )
Email: roy(dot)crombleholme(at)its(dot)lancscc(dot)gov(dot)uk

********************
This e-mail contains information intended for the addressee only.
It may be confidential and may be the subject of legal and/or professional privilege.
If you are not the addressee you are not authorised to disseminate, distribute, copy or use this e-mail or any attachment to it
The content may be personal or contain personal opinions and unless specifically stated or followed up in writing, the content cannot be taken to form a contract or to be an expression of the County Council's position.
LCC reserves the right to monitor all incoming and outgoing email
LCC has taken reasonable steps to ensure that outgoing communications do not contain malicious software and it is your responsibility to carry out any checks on this email before accepting the email and opening attachments.
********************