How to copy table from server to another in PostgreSQL?

Question

I have two servers on PostgreSQL 8.4:

server1:5432
server2:5432

Now i want to copy table from server1 and put it in data base on server2.
Its possible to do?

UPDATE

I try do like in @Valery Viktorovsky's answer:

pg_dump --server1:5432 --encoding=utf8 --no-owner --username=postgres --123456 np_point > D:\np_point.sql
psql --server2:5432 --username=postgres mrsk -f D:\np_point.sql

and get error:

ERROR:  syntax error at or near "pg_dump"
LINE 1: pg_dump --server1:5432 --encoding=utf8 --no-owner --use...

Answer 1

exact commands -

1. Export via pg_dump into a file:

    pg_dump --host "source hostname" --port 5432 --username "username" --no-password --verbose --file "filename" --table "source schema.tablename" "source db name"

this will create a file called "filename" at the directory where you ran above command - that will have schema and data for the source table. You can can give any absolute path too.

2. Import via psql:

    psql --host "target hostname" --port 5432 --username "username" --password --verbose --file "file name" "target db name"  

->this will prompt for password

Before running import, drop the target table if exists.

Worked like charm and finished 10M rows within 2mins

Answer 2

The safest way is to use pg_dump.

pg_dump --host server1 --encoding=utf8 --no-owner --username=foo --password -t table_name db_name > server1_db.sql
psql --host server2 --username=foo db_name -f server1_db.sql

Answer 3

I've written a small script in python that can help. Note - it will only work with small tables. You first need to pip install pandas, sqlalchemy and psycopg2. Create the file as pycopy.py and run it with "python pycopy.py tablename" (without the quotes). You can change the source and destination to work with any other db type - just change the source and destination strings.

The script:

import pandas as pd
import sys
import sqlalchemy as sa

strengine_source='postgresql://user:password@db_ip:5432/dbsourcename'
strengine_dest='postgresql://user:password@db_ip:5432/dbdestinationname'

if len(sys.argv) > 1:
        tblname = sys.argv[1]
        df=pd.read_sql("select * from " + tblname,sa.create_engine(strengine_source))
        df.to_sql(tblname,sa.create_engine(strengine_dest), index=False)