Copy data from one database to another postgress

Question

How do I copy data from a database table to another database table in postgress

It’s possible to do something like?

INSERT INTO V(ID,D,S)
VALUES(SELECT ID,D,S
FROM D.A)

Answer 1

You can do this with postgres via an extension called dblink the installation is in the postgres contrib or Shared extention folder, it is a script just run it and will be created 40 and few functions in the selected database.

The syntax is kind of verbose but in practice it is composed of 3 parts, the first is the setting of the Insert in the local database, the second is the setting of the remote connection and the select, the last is the typing of the return.

INSERT INTO tabela_local(id, nome, descricao, data)
    SELECT id, nome, descricao, data FROM 
    dblink('host=hostRemoto user=postgres password=senha dbname=base_remota'::text,
    'SELECT id, nome, descricao, data FROM tabela_remota'::text, false)
    tabela_temp(id integer, nome character varying, descricao character varying, data date)
)

Answer 2

You can simplify using this code by changing the values of the tables

$ pg_dump -U postgres -aD -t tb_tabela1-t tb_tabela2 database

Answer 3

I think you’ll need the dblink extension - dblink -- executes a query in a remote database

http://www.postgresql.org/docs/9.4/static/contrib-dblink-function.html

http://www.underdog-projects.net/2009/02/copy-a-table-across-databases-via-dblink/

insert into realtime (symbol,date,price)
select * from dblink('dbname=stocks',
              'select name,date,(bid+ask)/2 as price
              from realtime
              where date > to_date(''20081231'',''yyyyMMDD'') and date < to_date(''20090201'',''yyyyMMDD'')')
         as t1 (name character varying,date timestamp,price numeric);

Answer 4

It all depends on the periodicity of it.

If it is only once, you can take a backup of the table by pg_admin and a restore in the other bank.

If you want to send only a few records, you can use the command COPY. Below is an example:

COPY (select * from tabela) '/tmp/bkp_tabela'

If the bank you are going to do the restore is on the same server, just run the command

COPY nomeTabelaDestino TO '/tmp/bkp_tabela'

If the target database is on another server, just take the file bkp_tabela who is in the diretorio /tmp.

Answer 5

You can use dblink 1. First creates an extension with the help of a query in Postgres:

create Extension dblink 2. Immediately thereafter select [column name_name] from dblink('dbname=[path_name old] options=-csearch_path=_', 'select [column name_name] from [path_name new] ') as t ( [column name_name][data type that will be passed])

ex: select name from dblink('dbname=banco_-old options=-csearch_path=_', 'select name from public.social_person') as t (name text)