Re: pg_restore - table restoration - Mailing list pgsql-general

Adrian,

Thanks for confirming my observations.

My hope was that I would be able to create one archive file with pg_dump -Fc,
which at a future time could be used to do either a total restoration
or partial restorations via options of pg_restore; ie. Not to have create
addeded specialized pg_dump for each recovery case.

I had as you suggested observed stdout of my test cases.

a.) pg_restore -c -t tbl1 -t tbl2  archive_file
There are no SQL CONSTRAINT or TRIGGER statements related to these
2 tables.
When I add the "-d my_db" it confirms that table is restored,
But with no constraints and no triggers.

b.) pg_restore -c -v -a -t tbl2 -t tbl2 archive_file
As previously noted I get verbose indication that the table data is being dropped.
However there are no SQL commands that would cause that ( DELETE or TRUNCATE )
The attempt ends up failing as the table ends up with duplicated data.
This ( -a -c ) would be a nice combination of pg_restore as pg_dump as I recall
does not allow for that combination.



Rgds


Dave


-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@gmail.com]
Sent: Monday, January 13, 2014 7:36 PM
To: Day, David; pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_restore - table restoration

On 01/13/2014 01:43 PM, Day, David wrote:
> Hi,
>
> This is sort of a continuation of = problems I was working on last
> week
>
> with selective restorations of an archive file at the schema or table
> level.  ( V9.3)
>
> Given that I dumped the entire database ( pg_dump -Fc  my_db -f
> archive_file )
>
> When I pg_restore an entire schema ( -n ) everything is wonderful.
>
> If I try to attempt two tables in one of the schemas I encounter problems.
>
> I get a success of sort with these option  variations:
>
> pg_restore -c  -t tbl1 -t tbl2 -U <username> -d my_db  archive_file
>
> In this case the tables are recreated with data but all the original
>   constraints for these tables are missing
>
> As are triggers that are associated with the tables.   I guess I can
> understand this.

>
> Is this a bug or a mis-understanding on my part?


Oops, turns out I did not exactly replicate what you where doing and my previous answer is wrong.

What I found.

When I do this:

/usr/local/pgsql93/bin/pg_dump -Fc  -U hplc_admin -p 5452 -f hplc.out hplc

and then this:

/usr/local/pgsql93/bin/pg_restore -c -t student_sessions -t student_attendance -f hplc_table.sql hplc.out

I see what you see, no constraints or triggers in the SQL.

When I do what I originally posted about:

/usr/local/pgsql93/bin/pg_dump -Fc -c -t student_sessions -t student_attendance -U hplc_admin -p 5452 -f
hplc_tables_2.outhplc 

and then this:

/usr/local/pgsql93/bin/pg_restore -c -f hplc_table_2.sql hplc_tables_2.out

I do see the constraints and triggers in the output.

I was under the impression that the result should be the same for both.
So I would consider this puzzling at the least and a bug at the most.
Someone else will have to chime in on what is really happening because I do not know and I see nothing in the docs to
suggestthe behavior should be different. 


>
> Regards
>
> Dave Day
>


--
Adrian Klaver
adrian.klaver@gmail.com