Thread: 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
Attachment
"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