[Klug-general] Postgresql backup and restoration

[MacGyveR]

On Wednesday 27 Jan 2010, Karl Buckland wrote:
> 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
>
> _______________________________________________
> Kent mailing list
> Kent at mailman.lug.org.uk
> https://mailman.lug.org.uk/mailman/listinfo/kent

there is one thing worse than inserting non-UTF8 characters into a UTF8 
database, thats postgresql allowing you to insert characters from another 
characterset when not using utf8 eg, latin3 in a latin1 db, eeeww I had the 
fun of sorting that out..

-- 
--------------------------------
http://www.thedumbterminal.co.uk