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
Attachments:
"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 directoryYou 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