Обсуждение: pg_dump/pg_restore issues
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
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.
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
On 02/19/2014 10:08 AM, bricklen wrote:
Another alternative would be to add another backup: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.
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
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, >