Subject: Re: pg_dump fatal error
To: pgsql-admin@postgresql.org
References: 
 <CAE_gQfXD622Uv0o2N5fmbBTbEDH7=-0TWeRwYjXEUQXJo7bs_Q@mail.gmail.com>
 <56BCE798.1040008@free.fr>
From: =?UTF-8?Q?Pierre_Chevalier_G=c3=a9ologue?= <pierrechevaliergeol@free.fr>
Message-ID: <56BDA108.4040203@free.fr>
Date: Fri, 12 Feb 2016 10:08:24 +0100
User-Agent: Mozilla/5.0 (X11; Linux i686; rv:38.0) Gecko/20100101
 Icedove/38.5.0
MIME-Version: 1.0
In-Reply-To: <56BCE798.1040008@free.fr>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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