Re: backup and restore

On 15 jan, 12:29, d(dot)(dot)(dot)(at)archonet(dot)com (Richard Huxton) wrote:
> > It's quite simple to have problems with pgAdmin's backup procedure.
>
> Hmm - shouldn't be, and if so then please let the pgAdmin people know!
> They're always working to improve the package and it's
>

I think the same. We (company I work for) develop an ERP software with
more than 200 customers, and each one have a individual and
confidential database with PostgreSQL.
Using pgAdmin version 1.6.x or lower with those databases causes
errors when the database is restored from a .backup file created with
compression from pgAdmin. However, to kill the doubt, I realize those
errors with pg_restore utility from terminal as well.

> > Althought pgAdmin 3 uses pg_dump and pg_restore utilities, I could
> > report many errors when did backups and/or restores from it.
>
> Did you keep any notes, and can you reproduce them?

In fact, I do not keep notes from those errors because they're always
on database schema. The table and its data can be restored very well.
So we have a utility written in Visual Foxpro wich can read postgres
database schema and do the database diff to upgrade the restored db
with the schema needed (PKs, FKs, views, triggers, etc).
I will study these cases most extensively in our labs, and will take
notes to post them here (once I can prove it's not a pgAdmin problem
but pg_dump issue when using compressing - if not proved, I send it to
pgAdmin developers.).

> > I suggest you to use pg_dump command line utility into a plain SQL
> > file, so you can compress it with bzip2 to maximum compress rates.
> > When you restore, decompress the file (if you have packed it) and use
> > the psql to load the script file into the new and clean database.
>
> You're better off using the compressed mode imnsho. The flexibility is
> well worth any minor loss vs bzip2's compression. Check out the -l / -L
> options to let you pick and choose what you restore.

The only way I found to dump and restore databases with no errors is
using pg_dump creating a plain text file. Compression is a good way,
but I prefer create a plain text SQL file and compress it my way
after...
And maybe bzip2 couldn't be the best pratice, it was just a
suggestion ;)

P.S: I have to trace exactly these pgAdmin issues. The issues really
exists (I'll not post any fake message here if it was not true), but I
can't send databases dump to reproduce them according the contract
with our customers (confidential data).