Search Postgresql Archives

Re: pg_restore without dropping db/table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx


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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux