Thread: pg_dump/pg_restore issues

pg_dump/pg_restore issues

From
Leonardo M. Ramé
Date:
Hi, I'm backing up a big database using the --exclude-table option for
two tables, say table1 and table2. Then another backup of only those
tables, so, the final result are three backup files.

basic.backup
table1.backup
table2.backup

The problem I'm facing is at the restore moment is that basic.backup
contains view definitions related to table1 or table2, hence, the
restore does not create those views.

How do you recommend to workaround this?.

P.S.: I create three files because table1 and table2 are tables with
blob data, and we use basic.backup to create testing database where we
don't need blob data.

Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



Re: pg_dump/pg_restore issues

From
bricklen
Date:

On Wed, Feb 19, 2014 at 9:57 AM, Leonardo M. Ramé <l.rame@griensu.com> wrote:
Hi, I'm backing up a big database using the --exclude-table option for
two tables, say table1 and table2. Then another backup of only those
tables, so, the final result are three backup files.

basic.backup
table1.backup
table2.backup

The problem I'm facing is at the restore moment is that basic.backup
contains view definitions related to table1 or table2, hence, the
restore does not create those views.

How do you recommend to workaround this?.

P.S.: I create three files because table1 and table2 are tables with
blob data, and we use basic.backup to create testing database where we
don't need blob data.

The --section option of pg_dump might allow you dump the views separately.
Alternatively, if you know the names of the views that will fail, you could pg_dump as you are doing now, but in custom format (-Fc), then use pg_restore to create a list file from the contents, comment out the views, pg_restore using the list file (minus those views), then pg_dump using another list file with *only* those views.

Re: pg_dump/pg_restore issues

From
Leonardo M. Ramé
Date:
On 2014-02-19 10:08:19 -0800, bricklen wrote:
> On Wed, Feb 19, 2014 at 9:57 AM, Leonardo M. Ramé <l.rame@griensu.com>wrote:
>
> > Hi, I'm backing up a big database using the --exclude-table option for
> > two tables, say table1 and table2. Then another backup of only those
> > tables, so, the final result are three backup files.
> >
> > basic.backup
> > table1.backup
> > table2.backup
> >
> > The problem I'm facing is at the restore moment is that basic.backup
> > contains view definitions related to table1 or table2, hence, the
> > restore does not create those views.
> >
> > How do you recommend to workaround this?.
> >
> > P.S.: I create three files because table1 and table2 are tables with
> > blob data, and we use basic.backup to create testing database where we
> > don't need blob data.
> >
>
> The --section option of pg_dump might allow you dump the views separately.
> Alternatively, if you know the names of the views that will fail, you could
> pg_dump as you are doing now, but in custom format (-Fc), then use
> pg_restore to create a list file from the contents, comment out the views,
> pg_restore using the list file (minus those views), then pg_dump using
> another list file with *only* those views.

The good news are that I'm using -Fc, now I'll generate the list. I've
found there's a sequence related to one of those tables and I'm
wondering if there's a way to backup the sequence only.

Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



Re: pg_dump/pg_restore issues

From
Adrian Klaver
Date:
On 02/19/2014 10:08 AM, bricklen wrote:

On Wed, Feb 19, 2014 at 9:57 AM, Leonardo M. Ramé <l.rame@griensu.com> wrote:
Hi, I'm backing up a big database using the --exclude-table option for
two tables, say table1 and table2. Then another backup of only those
tables, so, the final result are three backup files.

basic.backup
table1.backup
table2.backup

The problem I'm facing is at the restore moment is that basic.backup
contains view definitions related to table1 or table2, hence, the
restore does not create those views.

How do you recommend to workaround this?.

P.S.: I create three files because table1 and table2 are tables with
blob data, and we use basic.backup to create testing database where we
don't need blob data.

The --section option of pg_dump might allow you dump the views separately.
Alternatively, if you know the names of the views that will fail, you could pg_dump as you are doing now, but in custom format (-Fc), then use pg_restore to create a list file from the contents, comment out the views, pg_restore using the list file (minus those views), then pg_dump using another list file with *only* those views.
Another alternative would be to add another backup:

pg_dump -s -t table1 -t table2 -f view.dump

This will dump the table definitions only which is all you need.

And then in order restore:

view.dump
basic.backup

Re: pg_dump/pg_restore issues

From
Leonardo M. Ramé
Date:
On 2014-02-19 10:23:58 -0800, Adrian Klaver wrote:
> On 02/19/2014 10:08 AM, bricklen wrote:
> >
> >On Wed, Feb 19, 2014 at 9:57 AM, Leonardo M. Ramé
> ><l.rame@griensu.com <mailto:l.rame@griensu.com>> wrote:
> >
> >    Hi, I'm backing up a big database using the --exclude-table option for
> >    two tables, say table1 and table2. Then another backup of only those
> >    tables, so, the final result are three backup files.
> >
> >    basic.backup
> >    table1.backup
> >    table2.backup
> >
> >    The problem I'm facing is at the restore moment is that basic.backup
> >    contains view definitions related to table1 or table2, hence, the
> >    restore does not create those views.
> >
> >    How do you recommend to workaround this?.
> >
> >    P.S.: I create three files because table1 and table2 are tables with
> >    blob data, and we use basic.backup to create testing database where we
> >    don't need blob data.
> >
> >
> >The --section option of pg_dump might allow you dump the views separately.
> >Alternatively, if you know the names of the views that will fail,
> >you could pg_dump as you are doing now, but in custom format
> >(-Fc), then use pg_restore to create a list file from the
> >contents, comment out the views, pg_restore using the list file
> >(minus those views), then pg_dump using another list file with
> >*only* those views.
> Another alternative would be to add another backup:
>
> pg_dump -s -t table1 -t table2 -f view.dump
>
> This will dump the table definitions only which is all you need.
>
> And then in order restore:
>
> view.dump
> basic.backup

That makes sense, I'll try it.

Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877



Re: pg_dump/pg_restore issues

From
Adrian Klaver
Date:
On 02/19/2014 10:32 AM, Leonardo M. Ramé wrote:
> On 2014-02-19 10:23:58 -0800, Adrian Klaver wrote:

>> Another alternative would be to add another backup:
>>
>> pg_dump -s -t table1 -t table2 -f view.dump
>>
>> This will dump the table definitions only which is all you need.
>>
>> And then in order restore:
>>
>> view.dump
>> basic.backup
>
> That makes sense, I'll try it.

You will still hit the sequence issue you mentioned previously, unless
you include it as one of the -t options. If you are using the -Fc option
already I would do as bricklen suggested and just use the TOC list. Then
you could include the sequence in the restore.

>
> Regards,
>