pg_restore without dropping db/table

On Thu, Mar 10, 2016 at 10:51:05AM -0500, anj patnaik wrote:

Does pg_restore only add new rows if I restore without deleting old db?

No. For one thing, pg_restore cannot know what you consider
to be a "new row".

Best,
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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

On 03/10/2016 09:41 AM, Karsten Hilbert wrote:

On Thu, Mar 10, 2016 at 10:51:05AM -0500, anj patnaik wrote:

Does pg_restore only add new rows if I restore without deleting old db?

No. For one thing, pg_restore cannot know what you consider
to be a "new row".

If you however do know what is new is then you might want to look at:

COPY
http://www.postgresql.org/docs/9.5/interactive/sql-copy.html
<<IMPORTANT>>
"COPY with a file name instructs the PostgreSQL server to directly read
from or write to a file. The file must be accessible by the PostgreSQL
user (the user ID the server runs as) and the name must be specified
from the viewpoint of the server. When PROGRAM is specified, the server
executes the given command and reads from the standard output of the
program, or writes to the standard input of the program. The command
must be specified from the viewpoint of the server, and be executable by
the PostgreSQL user. When STDIN or STDOUT is specified, data is
transmitted via the connection between the client and the server."

Or you can use the psql variant \copy

http://www.postgresql.org/docs/9.5/interactive/app-psql.html

"Performs a frontend (client) copy. This is an operation that runs an
SQL COPY command, but instead of the server reading or writing the
specified file, psql reads or writes the file and routes the data
between the server and the local file system. This means that file
accessibility and privileges are those of the local user, not the
server, and no SQL superuser privileges are required."

In either case you can craft a query to limit the rows copied. The issue
is that this assumes the both databases are static over the time you do
this.

Your other options are to use replication:

http://www.postgresql.org/docs/9.5/interactive/high-availability.html

or FDW

https://wiki.postgresql.org/wiki/Foreign_data_wrapper

Best,
Karsten

--
Adrian Klaver
adrian.klaver@aklaver.com

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

On Thu, Mar 10, 2016 at 12:53 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 03/10/2016 09:41 AM, Karsten Hilbert wrote:

On Thu, Mar 10, 2016 at 10:51:05AM -0500, anj patnaik wrote:

Does pg_restore only add new rows if I restore without deleting old db?

No. For one thing, pg_restore cannot know what you consider
to be a "new row".

If you however do know what is new is then you might want to look at:

COPY
http://www.postgresql.org/docs/9.5/interactive/sql-copy.html
<<IMPORTANT>>
"COPY with a file name instructs the PostgreSQL server to directly read
from or write to a file. The file must be accessible by the PostgreSQL user
(the user ID the server runs as) and the name must be specified from the
viewpoint of the server. When PROGRAM is specified, the server executes the
given command and reads from the standard output of the program, or writes
to the standard input of the program. The command must be specified from
the viewpoint of the server, and be executable by the PostgreSQL user. When
STDIN or STDOUT is specified, data is transmitted via the connection
between the client and the server."

Or you can use the psql variant \copy

http://www.postgresql.org/docs/9.5/interactive/app-psql.html

"Performs a frontend (client) copy. This is an operation that runs an SQL
COPY command, but instead of the server reading or writing the specified
file, psql reads or writes the file and routes the data between the server
and the local file system. This means that file accessibility and
privileges are those of the local user, not the server, and no SQL
superuser privileges are required."

In either case you can craft a query to limit the rows copied. The issue
is that this assumes the both databases are static over the time you do
this.

Your other options are to use replication:

http://www.postgresql.org/docs/9.5/interactive/high-availability.html

or FDW

https://wiki.postgresql.org/wiki/Foreign_data_wrapper

Best,
Karsten

--
Adrian Klaver
adrian.klaver@aklaver.com

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

The best way to accomplish what you want is to create a table with the same
structure in the first database as the one you want to restore to. Then you
can truncate that table, restore the data from the other db into it, and
use that to add the new rows to your table.
eg:
1. You have your original table:
CREATE TABLE orig_table
(prime_key varchar(10) ,
data_col1 integer,
data_col2 varchar(5),
CONSTRAINT orig_table_pk PRIMARY KEY (prime_key)
);
2. Duplicate table:
CREATE TABLE dup_table
(prime_key varchar(10) ,
data_col1 integer,
data_col2 varchar(5),
CONSTRAINT dup_table_pk PRIMARY KEY (prime_key)
);
3. pg_dump --format=custom -a -t orig_table second_db > your_dump.dmp
4. psql -d first_db -c truncate dup_table
5. edit your_dump.dmp and change all occurrences of orig_table to
dup_table.
6. pg_restore -a -d first_db -t dup_table your_dump.dmp
7. psql -d first_db
8. INSERT INTO orig_table
SELECT * FROM dup_table
WHERE dup.prime_key NOT IN (SELECT prime_key FROM orig_table);

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On Thu, Mar 10, 2016 at 01:49:42PM -0500, Melvin Davidson wrote:

The best way to accomplish what you want is to create a table with the same
structure in the first database as the one you want to restore to. Then you
can truncate that table, restore the data from the other db into it, and
use that to add the new rows to your table.
eg:
1. You have your original table:
CREATE TABLE orig_table
(prime_key varchar(10) ,
data_col1 integer,
data_col2 varchar(5),
CONSTRAINT orig_table_pk PRIMARY KEY (prime_key)
);
2. Duplicate table:
CREATE TABLE dup_table
(prime_key varchar(10) ,
data_col1 integer,
data_col2 varchar(5),
CONSTRAINT dup_table_pk PRIMARY KEY (prime_key)
);

This could benefit from

create table [...] like orig_table excluding all ...

8. INSERT INTO orig_table
SELECT * FROM dup_table
WHERE dup.prime_key NOT IN (SELECT prime_key FROM orig_table);

This will work if

dup.prime_key NOT IN (SELECT prime_key FROM orig_table)

identifies "new" rows. This probably has the highest chance
of being true if prime_key is a natural key.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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

fyi, since the version of PostgreSQL was NOT stated (or O/S) as is the
proper thing to do when posting, I gave a generic solution which covers all
versions and O/S's

On Thu, Mar 10, 2016 at 3:53 PM, Karsten Hilbert <Karsten.Hilbert@gmx.net>
wrote:

On Thu, Mar 10, 2016 at 01:49:42PM -0500, Melvin Davidson wrote:

The best way to accomplish what you want is to create a table with the

same

structure in the first database as the one you want to restore to. Then

you

can truncate that table, restore the data from the other db into it, and
use that to add the new rows to your table.
eg:
1. You have your original table:
CREATE TABLE orig_table
(prime_key varchar(10) ,
data_col1 integer,
data_col2 varchar(5),
CONSTRAINT orig_table_pk PRIMARY KEY (prime_key)
);
2. Duplicate table:
CREATE TABLE dup_table
(prime_key varchar(10) ,
data_col1 integer,
data_col2 varchar(5),
CONSTRAINT dup_table_pk PRIMARY KEY (prime_key)
);

This could benefit from

create table [...] like orig_table excluding all ...

8. INSERT INTO orig_table
SELECT * FROM dup_table
WHERE dup.prime_key NOT IN (SELECT prime_key FROM orig_table);

This will work if

dup.prime_key NOT IN (SELECT prime_key FROM orig_table)

identifies "new" rows. This probably has the highest chance
of being true if prime_key is a natural key.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On Thu, Mar 10, 2016 at 03:59:58PM -0500, Melvin Davidson wrote:

fyi, since the version of PostgreSQL was NOT stated (or O/S) as is the
proper thing to do when posting, I gave a generic solution which covers all
versions and O/S's

That's an important point.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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