Lists: | pgsql-general |
---|
From: | Nigel Heron <nigel(at)psycode(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | issue with pg_restore |
Date: | 2011-07-28 04:19:38 |
Message-ID: | 4E30E35A.4080205@psycode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hi list,
I'm trying to restore a backup into a database with a new name
the dump was done on a 8.4 server with:
pg_dump -F c -f bakfile olddb
i'm trying to restore it with:
createdb newdb; pg_restore -v --jobs=4 --disable-triggers
--no-tablespaces --dbname=newdb bakfile
or even just:
createdb newdb; pg_restore -v --dbname=newdb bakfile
It doesn't work .. pg_restore claims to be creating tables, indexes,
etc. and there are no errors in the output. It only takes a few seconds
to run (the file is ~250MB).
In newdb, all the tables in the "public" schema are missing. All the
functions and triggers were created though, tables in a non "public"
schema were created but don't contain data. Tried on 8.4 and on 9.0 with
the same result.
I turned on server statement logging and don't see statements that would
create the missing tables, there are alot of BEGIN/COMMIT statements
with nothing in between.
the only way i got it to work was to run:
pg_restore bakfile | psql newdb
which loads everything just fine but i was hoping to use parallel
restore to speed it up.
any ideas?
-nigel.
From: | Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Nigel Heron <nigel(at)psycode(dot)com> |
Subject: | Re: issue with pg_restore |
Date: | 2011-07-28 13:37:07 |
Message-ID: | 201107280637.07893.adrian.klaver@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Wednesday, July 27, 2011 9:19:38 pm Nigel Heron wrote:
> Hi list,
> I'm trying to restore a backup into a database with a new name
>
> the dump was done on a 8.4 server with:
> pg_dump -F c -f bakfile olddb
>
> i'm trying to restore it with:
> createdb newdb; pg_restore -v --jobs=4 --disable-triggers
> --no-tablespaces --dbname=newdb bakfile
> or even just:
> createdb newdb; pg_restore -v --dbname=newdb bakfile
>
> It doesn't work .. pg_restore claims to be creating tables, indexes,
> etc. and there are no errors in the output. It only takes a few seconds
> to run (the file is ~250MB).
> In newdb, all the tables in the "public" schema are missing. All the
> functions and triggers were created though, tables in a non "public"
> schema were created but don't contain data. Tried on 8.4 and on 9.0 with
> the same result.
> I turned on server statement logging and don't see statements that would
> create the missing tables, there are alot of BEGIN/COMMIT statements
> with nothing in between.
>
> the only way i got it to work was to run:
> pg_restore bakfile | psql newdb
> which loads everything just fine but i was hoping to use parallel
> restore to speed it up.
>
> any ideas?
>
> -nigel.
You running the pg_restore as postgres user with sufficient privileges?
You can do pg_restore -f bakfile.sql bakfile to have it restore to a text file
instead of a database. Might help in seeing what is going on.
--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org, Nigel Heron <nigel(at)psycode(dot)com> |
Subject: | Re: issue with pg_restore |
Date: | 2011-07-28 13:41:11 |
Message-ID: | 7066.1311860471@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> writes:
> On Wednesday, July 27, 2011 9:19:38 pm Nigel Heron wrote:
>> I'm trying to restore a backup into a database with a new name
>> It doesn't work .. pg_restore claims to be creating tables, indexes,
>> etc. and there are no errors in the output. It only takes a few seconds
>> to run (the file is ~250MB).
> You running the pg_restore as postgres user with sufficient privileges?
I'm wondering if it could be the same bug reported two days ago:
http://archives.postgresql.org/message-id/201107270042.22427.julian@mehnle.net
Have you got standard_conforming_strings turned on?
regards, tom lane
From: | Nigel Heron <nigel(at)psycode(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: issue with pg_restore |
Date: | 2011-07-29 02:38:17 |
Message-ID: | 4E321D19.9050701@psycode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 11-07-28 09:41 AM, Tom Lane wrote:
> Adrian Klaver<adrian(dot)klaver(at)gmail(dot)com> writes:
>> On Wednesday, July 27, 2011 9:19:38 pm Nigel Heron wrote:
>>> I'm trying to restore a backup into a database with a new name
>>> It doesn't work .. pg_restore claims to be creating tables, indexes,
>>> etc. and there are no errors in the output. It only takes a few seconds
>>> to run (the file is ~250MB).
>> You running the pg_restore as postgres user with sufficient privileges?
yes, i'm running it as the postgres superuser
> I'm wondering if it could be the same bug reported two days ago:
> http://archives.postgresql.org/message-id/201107270042.22427.julian@mehnle.net
> Have you got standard_conforming_strings turned on?
>
> regards, tom lane
That must be it! I do have standard_conforming_strings on. What i found
is a string ending with a backslash as a default in a column definition
.. so that bug must be more wide spread than just comments.
eg.
CREATE TABLE foo ( bar text DEFAULT '.\somepath\' );
thanks,
-nigel.
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Nigel Heron <nigel(at)psycode(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: issue with pg_restore |
Date: | 2011-07-29 02:50:54 |
Message-ID: | 12178.1311907854@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Nigel Heron <nigel(at)psycode(dot)com> writes:
> On 11-07-28 09:41 AM, Tom Lane wrote:
>> I'm wondering if it could be the same bug reported two days ago:
>> http://archives.postgresql.org/message-id/201107270042.22427.julian@mehnle.net
>> Have you got standard_conforming_strings turned on?
> That must be it! I do have standard_conforming_strings on. What i found
> is a string ending with a backslash as a default in a column definition
> .. so that bug must be more wide spread than just comments.
Yeah, actually it affects any situation where a string literal in the
SQL dump ends in a backslash. I've committed a patch for it, but in the
meantime the best workaround is to not use a direct-to-database restore,
but pipe the SQL output through psql.
regards, tom lane