From: Pierre Chevalier Géologue <pierrechevaliergeol@free.fr>
To: pgsql-admin@postgresql.org
Subject: Re: pg_dump fatal error
Date: Fri, 12 Feb 2016 10:08:24 +0100
Message-ID: <56BDA108.4040203@free.fr> (raw)
In-Reply-To: <56BCE798.1040008@free.fr>
References: <CAE_gQfXD622Uv0o2N5fmbBTbEDH7=-0TWeRwYjXEUQXJo7bs_Q@mail.gmail.com>
<56BCE798.1040008@free.fr>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Hi,
(sorry, I scr3w3d up *again* and I 'repl(ed)-to' Lucas only; Lucas, I
hope you don't mind that I get this conversation back in the community list)
Le 11/02/2016 20:34, drum.lucas@gmail.com a écrit :
> Hi there!
>
> hmm...
> yep.. this time I'm gonna do it different.
>
> I'll run the pg_Dump in the local machine, then copy it by networking to
> the new server
> and the restore...
>
> that should work :)
Yes, I think that's a quite safe way to go.
> Can I use /usr/pgsql-9.2/bin/pg_dump --exclude-table-data='junk.*' >
> bigdump.sql to a BIG sql file? like 1.7 TB?
If you have enough disk space available and your filesystem allows you
to have big files (which I'm pretty sure), that should not be any more
difficult than writing a smaller file.
I did similar tricks in the past, and I had some bugs... Most of them
were related to stupid stuff like a '\n' in a varchar, which was
misinterpreted at restore time, or some weird châràĉtêrs getting in the
way. The way I dealt with this was to split the dump file into two
parts: the structure, and the data (all the COPY statements, or INSERTS,
depending). I did that splitting by using combinations of grep, tail,
head, etc. Quite tedious, but it worked well.
Nowadays, there is an enhancement to pg_dump which allows to directly
dump into separate files the structure of the database and the tables'
contents. This is *very* handy.
In your case, that would be:
# dump the structure (small file):
ssh postgres@servidorslave01 "/usr/pgsql-9.2/bin/pg_dump -s
--exclude-table-data='junk.*'" > schema.sql
# dump the data (much bigger file):
ssh postgres@servidorslave01 "/usr/pgsql-9.2/bin/pg_dump -a
--exclude-table-data='junk.*'" > data.sql
# just to have a look at the dumps:
less schema.sql
less data.sql
# restore the database structure:
/usr/pgsql-9.2/bin/psql --dbname=uat_temp -f schema.sql
# and now the data contents:
/usr/pgsql-9.2/bin/psql --dbname=uat_temp -f data.sql
Now, if you encounter more bugs while restoring, you can refer to the
specific lines in the sql files, from the error messages. If you wish,
yo u may separate your data dumps into several ones, for instance one
per schema, stuff like that. It eases much the process of figuring out
where the restoration scr3w3d up.
À+
Pierre
Le 11/02/2016 20:57, Pierre Chevalier Géologue a écrit :
> Hello,
>
> What about trying to use a temporary file?
>
> Le 11/02/2016 19:30, drum.lucas@gmail.com a écrit :
>> I'm doing a copy (pg_dump) from a slave server to a test server.
>> The DB size is 1,7 TB, and I get the error at 1,4 TB.
>> Command:
>> ssh postgres@servidorslave01 "/usr/pgsql-9.2/bin/pg_dump
>> --exclude-table-data='junk.*' --format=custom db_live"
>> | /usr/pgsql-9.2/bin/pg_restore --dbname=uat_temp --exit-on-error
>>
>
> Something like:
>
> ssh postgres@servidorslave01
> /usr/pgsql-9.2/bin/pg_dump --exclude-table-data='junk.*' --format=custom
> db_live > bigdump
> /usr/pgsql-9.2/bin/pg_restore --dbname=uat_temp --exit-on-error bigdump
>
> (no guarantee at all, I'm just blindly guessing!)
>
>
> I usually do pg_dump's using plain SQL format: this allows to debug such
> nasty surprises. It sometimes helps. In your case, I would do
> something like:
>
> ssh postgres@servidorslave01
> /usr/pgsql-9.2/bin/pg_dump --exclude-table-data='junk.*' > bigdump.sql
> # just to have a look at the dump:
> less bigdump.sql
> /usr/pgsql-9.2/bin/psql --dbname=uat_temp -f bigdump
>
> À+
> Pierre
--
____________________________________________________________________________
Pierre Chevalier
PChGEI: Pierre Chevalier Géologue Et Informaticien
Mesté Duran
32100 Condom
Tél+fax : 09 75 27 45 62
06 37 80 33 64
Émail : pierrechevaliergeolCHEZfree.fr
icq# : 10432285
jabber: pierre.chevalier1967@jabber.fr
http://pierremariechevalier.free.fr/pierre_chevalier_geologue
____________________________________________________________________________
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: pierrechevaliergeol@free.fr
Subject: Re: pg_dump fatal error
In-Reply-To: <56BDA108.4040203@free.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox