Re: pg_dump/pg_restore issues

Adrian Klaver <adrian.klaver@xxxxxxxxxxx> · Wed, 19 Feb 2014 10:23:58 -0800



    On 02/19/2014 10:08 AM, bricklen wrote:

    
          On Wed, Feb 19, 2014 at 9:57 AM,
            Leonardo M. Ramé <l.rame@xxxxxxxxxxx> 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