pg_dump/pg_restore issues

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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,

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general