[Klug-general] Postgresql backup and restoration

[Karl Buckland]

2010/1/27 MacGyveR <macgyver at thedumbterminal.co.uk>:
>
> are you using pgdump_all, even though this will dup other db's too the
> database you require can be "cut" out of the file (an editor better than vi is
> required if the dump file is greater than 2gb) ? i would bother using sed as
> you can easily rename a database once it has been restored if you are
> restoring onto another system. also have you tried using the --disable-
> triggers option. you could also try the -d or -D options but this will be very
> slow. also when piping into psql you can set the verbosity level to echo every
> line and stop on an error, so you know exactly where the problem is.
>

I'm using pg_dump rather than pgdump_all, to only get one database.
Cutting the database I want out seems slightly crazy and unnecessarily
difficult. The dumpfile is 5Gb or so. Unfortunately I'm copying the
database to another database on the same Postgresql server, so it does
have to be renamed.

Yes, the --disable-triggers option turned out to be my friend after I
discovered it yeterday. Am I right in thinking that using -d or -D
would probably mean it wouild take hours, possibly days to recreate
this database? That's not really very convenient.

However, I think I managed to come up with a solution yesterday, which
I'll post here for the benefit of others. I'd still be very curious to
hear if there's an easier way though.

My method:
Where hostname, username, fromdb and todb, paths, etc are your own values.

First, export the database 'fromdb' ensuring UTF8 encoding. The file
format will be a tar for pg_dump.
nohup nice /path/to/postgresql/bin/pg_dump -h hostname -U username
--file=fromdb.db --blobs --format=tc --encoding=UTF8 fromdb &

Use pg_restore to produce an SQL script to re-insert the data, and
disable any triggers whilst the data is inserted to the DB.
nohup nice /path/to/postgresql/bin/pg_restore -f db_insert_script.sql
-X disable-triggers fromdb.db

Use iconv-chunks, a Perl script, to pass the SQL inserts through iconv
and ensure there are no illegal non-UTF8 characters which may stop the
import. We do this because iconv reads the whole file into memory,
which isn't sensible.
wget http://maurice.aubrey.googlepages.com/iconv-chunks.txt
mv iconv-chunks.txt iconv-chunks.pl
chmod +x iconv-chunks.pl
./iconv-chunks db_insert_script.sql -c -f windows-1251 -t utf-8 >
db_insert_script_utf8.sql

Import the data into the database 'todb'.
cat db_insert_script_utf8.sql | /path/to/postgresql/bin/psql -h
hostname -U username -d todb

Obviously if you're not using UTF-8 then that part will not apply, but
another encoding type may apply instead. The reason for stripping
non-UTF8 characters is that for some reason Postgresql allows you to
insert non-UTF8 characters into a UTF8 database. Why?!

It took me several hours to figure this out yesterday. Why is it so
complicated and badly documented? Just as a reminder, in MySQL you
simply do:
mysqldump -h hostname -U username -p fromdb > fromdb.db
mysql -h hostname -U username -p todb < fromdb.db
And that always seems to work...

Karl