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
