Обсуждение: cant get pg_dump/pg_restore to behave

i'm trying to add the ability to dump our database as a backup in case
things go wrong with the db server, and so i'm trying to test things
now so that if/when things do go bad, i'm not scrambling then :)

as a test, i'm just trying to dump a database's schema and restore
that ... but it seems like pg_dump doesnt dump things in order so when
i restore the dump, i get bunches of errors about things not existing
... looking at the actual dump, i can see the tables pg_restore is
complaining about have operations run on it before the actual CREATE
sql ...

$ pg_dump -F c -s -d database-server mydb > mydb.schema
$ psql -d mydb < mydb.schema
<error about users_idx not existing>
$ grep users_idx mydb.schema
 INSERT INTO users_idx (....
 UPDATE users_idx SET ...
 -- Name: users_idx; Type: TABLE; ...
 CREATE TABLE users_idx (...

err, shouldnt that CREATE be first ?
-mike

"Mike Frysinger" <vapier.adi@gmail.com> writes:
> $ pg_dump -F c -s -d database-server mydb > mydb.schema
> $ psql -d mydb < mydb.schema
> <error about users_idx not existing>

pg_dump -Fc does not produce a file that psql can read directly.
Is the above really what you did?

            regards, tom lane

On 4/20/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Mike Frysinger" <vapier.adi@gmail.com> writes:
> > $ pg_dump -F c -s -d database-server mydb > mydb.schema
> > $ psql -d mydb < mydb.schema
> > <error about users_idx not existing>
>
> pg_dump -Fc does not produce a file that psql can read directly.
> Is the above really what you did?

i was experimenting with using pg_dump/pg_restore and pg_dump/psql ...
when using psql to import, i didnt use -Fc ... but the errors were the
same regardless of whether i used pgsl or pg_restore ;(
-mike

"Mike Frysinger" <vapier.adi@gmail.com> writes:
> i was experimenting with using pg_dump/pg_restore and pg_dump/psql ...
> when using psql to import, i didnt use -Fc ... but the errors were the
> same regardless of whether i used pgsl or pg_restore ;(

Well, the whole thing is pretty strange, because AFAICS pg_dump will
never emit an UPDATE on a user table at all.  What PG version is this
exactly?

            regards, tom lane

On 4/20/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Mike Frysinger" <vapier.adi@gmail.com> writes:
> > i was experimenting with using pg_dump/pg_restore and pg_dump/psql ...
> > when using psql to import, i didnt use -Fc ... but the errors were the
> > same regardless of whether i used pgsl or pg_restore ;(
>
> Well, the whole thing is pretty strange, because AFAICS pg_dump will
> never emit an UPDATE on a user table at all.

this was my understanding of pg_dump as well ...

> What PG version is this exactly?

latest version on Fedora Core 6 - 8.1.8

ive been trying to use the documentation to do backup/restores:
http://www.postgresql.org/docs/8.1/interactive/backup.html

is there something obvious i'm missing here ?
-mike

"Mike Frysinger" <vapier.adi@gmail.com> writes:
> On 4/20/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Well, the whole thing is pretty strange, because AFAICS pg_dump will
>> never emit an UPDATE on a user table at all.

> this was my understanding of pg_dump as well ...

>> What PG version is this exactly?

> latest version on Fedora Core 6 - 8.1.8

Hmph.  It should pretty much just work ... and there is *definitely* not
any update command visible in the source code.

If there's not anything confidential about your schema, could you send
me the output of "pg_dump -s" on the problem database?  Maybe seeing a
fuller picture will yield a clue.

            regards, tom lane

On 4/20/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Hmph.  It should pretty much just work ... and there is *definitely* not
> any update command visible in the source code.

i dug deeper (like i should have in the first place) and the UPDATEs
are ok ... they're inside of functions which get triggered on events

> If there's not anything confidential about your schema, could you send
> me the output of "pg_dump -s" on the problem database?  Maybe seeing a
> fuller picture will yield a clue.

the schema shouldnt be a problem ... just the data :)

thanks for any insight ... ive pretty lost ;(
-mike

"Mike Frysinger" <vapier.adi@gmail.com> writes:
> On 4/20/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Hmph.  It should pretty much just work ... and there is *definitely* not
>> any update command visible in the source code.

> i dug deeper (like i should have in the first place) and the UPDATEs
> are ok ... they're inside of functions which get triggered on events

Doh, I should have thought of that.

>> If there's not anything confidential about your schema, could you send
>> me the output of "pg_dump -s" on the problem database?  Maybe seeing a
>> fuller picture will yield a clue.

> the schema shouldnt be a problem ... just the data :)

Well, I loaded and dumped and reloaded this schema in 8.1 without any
problem, so I'm still baffled.

Looking back at your original message, you say

>> $ pg_dump -F c -s -d database-server mydb > mydb.schema
>> $ psql -d mydb < mydb.schema
>> <error about users_idx not existing>

There are several obvious things wrong with that (eg, psql cannot read
-Fc format dumps) so I suppose it's an editorialization on what you
really typed.  Perhaps the problem is hidden there.  Can you show us an
*exact* transcript of a failing session?

            regards, tom lane

On 5/4/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> There are several obvious things wrong with that (eg, psql cannot read
> -Fc format dumps) so I suppose it's an editorialization on what you
> really typed.

right, what i posted was a typo, what i ran did not have the -Fc

> Perhaps the problem is hidden there.  Can you show us an
> *exact* transcript of a failing session?

[postgres@backup 0 ~]$ psql -q
postgres=# DROP DATABASE gforge5;
postgres=# CREATE DATABASE gforge5 WITH TEMPLATE = template0 ENCODING = 'UTF8';
postgres=#
[postgres@backup 0 ~]$ psql -d gforge5 -f gforge.schema
SET
SET
SET
COMMENT
CREATE LANGUAGE
SET
psql:gforge.schema:31: ERROR:  could not access file
"$libdir/tsearch2": No such file or directory
psql:gforge.schema:34: ERROR:  function public.gtsvector_in(cstring)
does not exist
psql:gforge.schema:42: ERROR:  type gtsvector does not exist
...
-mike

On 5/4/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Well, I loaded and dumped and reloaded this schema in 8.1 without any
> problem, so I'm still baffled.

oh, and the machine that i created the dump on and the machine i
loaded the dump on are both Fedora Core 6 that report:
$ postgres --version
postgres (PostgreSQL) 8.1.8
-mike

"Mike Frysinger" <vapier.adi@gmail.com> writes:
> [postgres@backup 0 ~]$ psql -d gforge5 -f gforge.schema
> ...
> psql:gforge.schema:31: ERROR:  could not access file
> "$libdir/tsearch2": No such file or directory

You don't have tsearch2 installed in the new installation.

            regards, tom lane

On 5/4/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Mike Frysinger" <vapier.adi@gmail.com> writes:
> > [postgres@backup 0 ~]$ psql -d gforge5 -f gforge.schema
> > ...
> > psql:gforge.schema:31: ERROR:  could not access file
> > "$libdir/tsearch2": No such file or directory
>
> You don't have tsearch2 installed in the new installation.

looks like it's provided by "postgresql-contrib" ... sorry i guess my
unfamiliarity with postgres shows as i didnt know that this "tsearch2"
was a postrgres thing

installing that package fixes all the errors (except missing gforge
role, but that one i can handle)

sorry for the protracted thread and thanks for your help :)
-mike