Transfer Data Between Databases with Postgresql

PostgreSQL copy/transfer data from one database to another

This is a really straightforward task. Just use dblink for this purpose:

INSERT INTO t(a, b, c)
SELECT a, b, c FROM dblink('host=xxx user=xxx password=xxx dbname=xxx', 'SELECT a, b, c FROM t') AS x(a integer, b integer, c integer)

If you need to fetch data from external database on a regular basis, it would be wise to define a server and user mapping. Then, you could use shorter statement:

dblink('yourdbname', 'your query')

Copy a table from one database to another in Postgres

Extract the table and pipe it directly to the target database:

pg_dump -t table_to_copy source_db | psql target_db

Note: If the other database already has the table set up, you should use the -a flag to import data only, else you may see weird errors like "Out of memory":

pg_dump -a -t table_to_copy source_db | psql target_db

Transferring data from one database to another (Postgres)

Yes, backup using "PLAIN" format (SQL statements) and then (when connected to the other DB) open the file and run it.

Or you could select "COMPRESS" format in the "backup" dialogue, and then you could use the restore dialogue.

Also there's an equivalent of phpMyAdmin for Postgres, called "phppgadmin". Select the table in question and then use the "Export" tab.

How can I migrate data from one PostgreSQL database to another (with slightly different table/column names)?

Use dblink to add data to new tables in the new database.

Example:

INSERT into new_table ( cd_ace, no_desc )
SELECT cd_accessory, no_description
FROM DBLINK('host=ip_address_remote port=5470 dbname=database_name user=user password=password ',
'SELECT cd_acessorio, no_description from dbatez.acessorio')
  AS a ( cd_accessory character varying(4), no_description character varying(40));

I hope I helped you.

PostgreSQL: How to copy data from one database table to another database

In the case the two databases are on two different server instances, you could export in CSV from db1 and then import the data in db2 :

COPY (SELECT * FROM t1) TO '/home/export.csv';

and then load back into db2 :

COPY t2 FROM '/home/export.csv';

Again, the two tables on the two different database instances must have the same structure.

Using the command line tools : pg_dump and psql , you could do even in this way :

pg_dump -U postgres -t t1 db1 | psql -U postgres -d db2

You can specify command line arguments to both pg_dump and psql to specify the address and/or port of the server .

Another option would be to use an external tool like : openDBcopy, to perform the migration/copy of the table.

How to migrate data between Postgres Servers which both need password?

Use a password file to store the passwords for both databases, then the PostgreSQL client will be able to connect to both without prompting for a password.

The password file would look like this:

localhost:5432:dbname:localuser:password1
remotehost:5432:dbname:remoteuser:password2

---

Related Topics