Thread: 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.

I just tried something similar here and I am not seeing that. I see
constraints and triggers.

Is there anything in the log that might help?


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

One thing you can do to help see what is going on is restore to a file
instead of the database. This creates a plain text file with the SQL
statements. So:

  pg_restore -c  -t tbl1 –t tbl2 –U <username> -f plain_text.sql
archive_file

>
> Regards
>
> Dave Day
>


--
Adrian Klaver
adrian.klaver@gmail.com

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.out hplc

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 suggest the behavior should
be different.


>
> Regards
>
> Dave Day
>


--
Adrian Klaver
adrian.klaver@gmail.com

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

On 01/14/2014 06:06 AM, Day, David wrote:
> 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.

Actually my suggestion was to use -f which captures the restore into a
file. This creates something you can look at leisure:)

>
> 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 )

Yes, it is outputting dropping TABLE DATA, where TABLE DATA is a command
I am not familiar with and which does not show up in the dump file.


> 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.

 From what I see it does not actually 'drop' the table data, so you are
just doing a COPY over existing data.

>
>
>
> Rgds
>
>
> Dave
>



--
Adrian Klaver
adrian.klaver@gmail.com

I note for the observed failure of pg_restore -c -a -t,
I have worked around this by performing a
Truncate operation on the tables prior to invoking
pg_restore with a simpler -a -t option combination.

If this matter needs to be reposted as bug or needs
further action/information from me,  please advise.

Thanks

Dave

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@gmail.com]
Sent: Tuesday, January 14, 2014 10:33 AM
To: Day, David; pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_restore - table restoration

On 01/14/2014 06:06 AM, Day, David wrote:
> 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.

Actually my suggestion was to use -f which captures the restore into a file. This creates something you can look at
leisure:)

>
> 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 )

Yes, it is outputting dropping TABLE DATA, where TABLE DATA is a command I am not familiar with and which does not show
upin the dump file. 


> 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.

 From what I see it does not actually 'drop' the table data, so you are
just doing a COPY over existing data.

>
>
>
> Rgds
>
>
> Dave
>



--
Adrian Klaver
adrian.klaver@gmail.com

On 01/14/2014 02:29 PM, Day, David wrote:
> I note for the observed failure of pg_restore -c -a -t,
> I have worked around this by performing a
> Truncate operation on the tables prior to invoking
> pg_restore with a simpler -a -t option combination.
>
> If this matter needs to be reposted as bug or needs
> further action/information from me,  please advise.

Well the bug if there is one would to me be the dropping TABLE DATA below:

aklaver@panda:~> /usr/local/pgsql93/bin/pg_restore -c -v -a -t
student_sessions -t student_attendance -d hplc -U hplc_admin -p 5452
hplc.out
pg_restore: connecting to database for restore
pg_restore: dropping TABLE DATA student_sessions
pg_restore: dropping TABLE DATA student_attendance
pg_restore: processing data for table "student_attendance"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3145; 0 21204 TABLE
DATA student_attendance hplc_admin
pg_restore: [archiver (db)] COPY failed for table "student_attendance":
ERROR:  duplicate key value violates unique constraint
"student_attendance_pkey"
DETAIL:  Key (attendance_id)=(1) already exists.

Seems to me pg_restore either does what it reports, drop the data, or it
does not report dropping TABLE DATA because that is not actually happening.

At this point this has reached my limits of understanding. I would say
file a bug report, if for no other reason then to get a clarification on
what the behavior in this situation is meant to be.


>
> Thanks
>
> Dave
>



--
Adrian Klaver
adrian.klaver@gmail.com