Обсуждение: Problem Backing Up a DB

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

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

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.

Cheers,
Pete


C:\Programme\PostgreSQL\8.1\bin>psql -f C:/pgsqldump_20-04-07.sql authdb
Password:
You are now connected to database "template1".
psql:C:/pgsqldump_20-04-07.sql:11: ERROR:  cannot delete from a view
HINT:  You need an unconditional ON DELETE DO INSTEAD rule.
psql:C:/pgsqldump_20-04-07.sql:13: NOTICE:  SYSID can no longer be specified
psql:C:/pgsqldump_20-04-07.sql:13: ERROR:  role "admin" already exists
psql:C:/pgsqldump_20-04-07.sql:14: NOTICE:  SYSID can no longer be specified
psql:C:/pgsqldump_20-04-07.sql:14: ERROR:  role "foo" already exists
psql:C:/pgsqldump_20-04-07.sql:15: NOTICE:  SYSID can no longer be specified
psql:C:/pgsqldump_20-04-07.sql:15: ERROR:  role "www" already exists
psql:C:/pgsqldump_20-04-07.sql:22: ERROR:  cannot delete from a view
HINT:  You need an unconditional ON DELETE DO INSTEAD rule.
psql:C:/pgsqldump_20-04-07.sql:24: NOTICE:  SYSID can no longer be specified
psql:C:/pgsqldump_20-04-07.sql:24: ERROR:  role "www" already exists
psql:C:/pgsqldump_20-04-07.sql:25: ERROR:  role "www" is a member of role
"www"
psql:C:/pgsqldump_20-04-07.sql:32: ERROR:  database "authdb" already exists
You are now connected to database "authdb".
SET
SET
SET
REVOKE
GRANT
SET
SET
psql:C:/pgsqldump_20-04-07.sql:83: NOTICE:  CREATE TABLE will create
implicit sequence "user_aut
umn "user_auth.id"
CREATE TABLE
REVOKE
GRANT
CREATE TABLE
CREATE TABLE
REVOKE
GRANT
SET
psql:C:/pgsqldump_20-04-07.sql:157: NOTICE:  CREATE TABLE will create
implicit sequence "login_i
 "login.id"
CREATE TABLE
SET
psql:C:/pgsqldump_20-04-07.sql:173: NOTICE:  CREATE TABLE will create
implicit sequence "antwort
lumn "antworten.id"
CREATE TABLE
psql:C:/pgsqldump_20-04-07.sql:188: NOTICE:  CREATE TABLE will create
implicit sequence "beitrae
lumn "beitraege.id"
CREATE TABLE
SET
psql:C:/pgsqldump_20-04-07.sql:203: NOTICE:  CREATE TABLE will create
implicit sequence "user_lo
umn "user_log.id"
CREATE TABLE
REVOKE
SET
SET
SET
SET
SET
psql:C:/pgsqldump_20-04-07.sql:5010: NOTICE:  ALTER TABLE / ADD PRIMARY KEY
will create implicit
 table "user_auth"
ALTER TABLE
psql:C:/pgsqldump_20-04-07.sql:5019: NOTICE:  ALTER TABLE / ADD PRIMARY KEY
will create implicit
le "contentlist_summary"
ALTER TABLE
psql:C:/pgsqldump_20-04-07.sql:5028: NOTICE:  ALTER TABLE / ADD PRIMARY KEY
will create implicit
table "user_data"
ALTER TABLE
SET
psql:C:/pgsqldump_20-04-07.sql:5039: NOTICE:  ALTER TABLE / ADD PRIMARY KEY
will create implicit
ble "login"
ALTER TABLE
SET
psql:C:/pgsqldump_20-04-07.sql:5050: NOTICE:  ALTER TABLE / ADD PRIMARY KEY
will create implicit
for table "antworten"
ALTER TABLE
psql:C:/pgsqldump_20-04-07.sql:5059: NOTICE:  ALTER TABLE / ADD PRIMARY KEY
will create implicit
for table "beitraege"
ALTER TABLE
SET
psql:C:/pgsqldump_20-04-07.sql:5070: NOTICE:  ALTER TABLE / ADD PRIMARY KEY
will create implicit
or table "user_log"
ALTER TABLE
SET
psql:C:/pgsqldump_20-04-07.sql:5080: ERROR:  relation "user_auth_id2_seq"
does not exist
 setval
--------
     58
(1 row)

 setval
--------
      1
(1 row)

 setval
--------
      1
(1 row)

SET
 setval
--------
     28
(1 row)

SET
COMMENT
You are now connected to database "template1".
SET
SET
SET
REVOKE
GRANT
SET
COMMENT
SET
COMMENT

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

Don't forget to cc: the mailing list.

Peter Neu wrote:
> Hello,
>
> thanks for the fast reply. This works. I don't really need the dumpall
> because I usually just back up one db.
>
> I noticed the dump is now a binary file. Means that I will have to migrate
> to 7.4.17 if this is the preferable release because 7.4.2 can't read the
> binary file I suppose.

Backup files in general don't work between versions. This is why you
were having problems. However, pg_dump knows about older versions of the
database and can read from them. If you want to transfer data from 7.4
to 8.1, use the pg_dump/pg_restore from 8.1. If you want to go backwards
(8.1 to 7.4) you can't (in general - you can usually tweak an SQL file
to get you there).

The binary file can be used to generate your SQL with INSERTs or COPY
commands as desired, or even do selective restores (--list/--use-list)
direct to a database.

> The windows box was just for testing purposes. The productive database will
> run again on a linux machine if the current one fails.
>
> So, is this problem I encountered just happening because I switched from 7.x
> to the 8.x release or is there a general problem with this particular
> PostgreSQL release?

A dump for one 7.4 database should transfer to another 7.4 database just
fine.

> The standby linux machine would also run 7.4.2 because it ships with SuSE ES
> 9.

Presumably ES9 includes bugfixes, so it must include a version later
than 7.4.2 (or perhaps have its own numbering system for updates e.g.
7.4.2-11).

If not, I'd suggest:
1. Cancelling your subscription for SuSE support and spending the money
on a crate of good single-malt whisky(*) instead. The whisky will
provide the same level of reliability for your database and is also more
sociable.
2. Upgrade to 7.4.17 - that way you'll have the 15 different sets of bug
fixes that the developers have provided. If you want to know what they
all are, go here:
http://www.postgresql.org/docs/7.4/static/release.html#RELEASE-7-4-17
Some of them are important to have, so you *do* want them.

(*) Other beverages are available. If you do not partake of alcohol,
consider a small artwork instead.

HTH
--
   Richard Huxton
   Archonet Ltd