Copy data from one database to another postgress

It is possible to do this with postgres through an extension called dblink the installation is in the contrib or shared \ extention folder of postgres, it is a script just run it and will create 40 and few functions in the selected database.

The syntax is a bit verbose but in practice it is composed of 3 parts, the first is the definition of the insert in the local database, the second is the definition of the remote connection and select, the last is the typing of the return. p>

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

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

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

I think you'll need the extension dblink - dblink - execute a query in a remote database

link

link

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

It all depends on the periodicity of this.

If it is only one time, you can back up the table by pg_admin and restore in the other bank.

If you want to send only a few records, you can use the COPY command. Here's an example below:

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

If the database you want to make restore is on the same server, just run the

COPY nomeTabelaDestino TO '/tmp/bkp_tabela'

If the destination bank is on another server, just take the file bkp_tabela that is in diretorio /tmp .