Re: Problem Backing Up a DB

Peter Neu wrote:
> Hello,
>
> I have a problem with backing up a database. The original db runs on a SuSE
> ES 9 box in Unicode. Version 7.4.2

If you're keeping this installation, see about upgrading to the latest 7.4.x

> The recovery db is supposed to run on a windows xp box. The encoding is
> UTF-8. Version 8.1
>
> On the linux box I did:
> pg_dumpall > outfile
>
> On the windows box I did:
>
> psql -f outfile authdb

OK. First, it's always better to use a more recent pg_dump if possible.
If your linux box is accessible from the XP machine you can just do
something like:

C:\Program File\...\pg_dump -h <linux-box-name-or-ip> -U postgres -d
<dbname> -F c > mydb.dump

Secondly, you probably want to dump the single database and then any
users separately. Do this as a superuser (e.g. postgres) and use the
custom format (-F c) for maximum flexibility.

Then you can use pg_restore to restore to your target DB.

> Problem is when I reload the db in this manner the sequences for the id
> column gets messed up ("user_auth_id2_seq"). The count starts from 1 again
> when I insert a new row. :o(
>
> For error output please see below. I already created the empty db authdb on
> the recovery instance because otherwise an error is thrown.

pg_restore can create the DB for you.

Well, it looks like setval() is being called at least four times, and
two of those have values > 1.

> setval
> --------
> 58
> setval
> --------
> 1
> setval
> --------
> 1
> setval
> --------
> 28

Try a later version of pg_dump and see if that does the trick.

--
Richard Huxton
Archonet Ltd