pg_dumpall

Hi Bric List!

I'm trying to migrate our current bric db to a new server
and I need to
verify that the backup and restore process worked properly
before I turn it
all on.

I thought dumping the old db (pgsql 8.1), restoring it to
the new db (pgsql
8.2), dumping the new db, then comparing the old and new
dump sizes for
equality would tell me if the restore worked. Unfortunately,
when I tried,
the old and new dump sizes were significantly different.

Going back through the output of psql < old_dump.sql I
found many syntax
errors, so I suspect that the database was not properly
reconstructed. Here
is the first error:

ERROR:  parameter "standard_conforming_strings"
cannot be changed

This was a common later error:

ERROR:  syntax error at or near "seq_user_member"
LINE 1: REVOKE ALL ON SEQUENCE seq_user_member FROM PUBLIC;

So my questions are:

Is my test a valid one?

Is there another way of verifying the success of a
backup/restore process
other than what I've devised?

What is causing pg_dumpall to generate a sql file full of
syntax errors?

Here's a sample of what I've been doing for reference:

# pg_dumpall -h db.server.com -U user bric > db_1.sql
# psql -U user bric < db_1.sql
# pg_dumpall -U user bric > db_2.sql
# ls -l
-rw-r--r-- 1 user user 272301201 db_1.sql
-rw-r--r-- 1 user user 272274461 db_2.sql

Thanks in advance!
Ryan

On Feb 6, 2007, at 2:07 PM, Ryan O'Toole wrote:

> I'm not stopping the database before performing the
dump, because  
> it seemed
> like it wasn't necessary from reading the DBA (
> http
://bricolage.cc/docs/1.10/api/::docs::current::api::Bric:BA).

>
> But maybe I should be?

If you stop the database, you won't be able to make the
dump. But if  
you're actually moving the Bricolage database to another
box, you'll  
want to shut down Bricolage before you dump, and start it up
pointing  
at the new database after the dump.

Best,

David

I was able to fix the problem by uninstalling postgres 8.2
and going back to
8.1, same version as my other db. I'm still not sure what
they issue was,
perhaps some installation error on my part. Anyways
everything is working
smoothly now.

Thanks for the helpful suggestions,
Ryan

On 2/6/07, David E. Wheeler <davidkineticode.com> wrote:
>
> On Feb 6, 2007, at 2:07 PM, Ryan O'Toole wrote:
>
> > I'm not stopping the database before performing
the dump, because
> > it seemed
> > like it wasn't necessary from reading the DBA (
> > http
://bricolage.cc/docs/1.10/api/::docs::current::api::Bric:BA).

> >
> > But maybe I should be?
>
> If you stop the database, you won't be able to make the
dump. But if
> you're actually moving the Bricolage database to
another box, you'll
> want to shut down Bricolage before you dump, and start
it up pointing
> at the new database after the dump.
>
> Best,
>
> David
>