copy table from one database to another

Hello,

I need to copy one table (properties and data both) from one database to another. I am using pgAdmin III.
I tried doing it with pg_dump and psql, but I guess I don't have the correct syntax.

Table_name :  table1
source database name: test_db
dest. database name: prod_db

In both the databases, the schema is public. and these 2 databases are on different machines.

Please help. Thank you.
enggAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ivanovnConnect With a Mentor Commented:
Alternatively you can use dblink function to transfer data and table structure.

For example if you want your table called t1 in database d1 to be reproduced as table t2 in database d2 you would do something like:

CREATE TABLE d2 AS SELECT <field_names>
FROM dblink('hostaddr=<src_ip> dbname=<src_db_name> user=<db_username> password=<db_password>', SELECT <field_names> FROM d1)
AS f(<field_names> <field_data_type>)

You just need to replace values in <> brackets to the real values.
0
 
enggAuthor Commented:
Thank you, earthman2.

I am using pgadmin III 1.4.0 and postgres 8.
I think pgAdmin is not recognising the pg_dump command. I am getting this error all the time.

ERROR:  syntax error at or near "pg_dump" at character 1.

I am using this command.

pg_dump --table=table1 --oids test_db | psql -d prod_db -h host_prod -p 5432 -U myusername -n -q

what is '-n' in this command?

Thank you.

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ivanovnCommented:
pg_dump is a utility in your postgres bin directory. It's not an SQL command. So what you need to do is execute it from the command line. Use what earthman2 gave you from the command line.

I believe -n disables enhanced command line editing.

To see all the pg_dump explanations go to the documentation page:
http://www.postgresql.org/docs/8.1/interactive/app-pgdump.html
0
 
earth man2Commented:
pg_dump and psql are command line utilities.
psql -n is used preferentially so you are not storing command line history ( which you do not need if you are operating in batch mode ).
0
 
enggAuthor Commented:
Thanks, ivanovn and earthman2.
is there a way to copy the table structure and a few rows of the data (based on a select query)?  
Would 'dblink' be able to do that?
I don't have a command line interface for postgres right now. I will get back to you soon.
thank you.
0
 
ivanovnCommented:
Yes you could use the dblink feature described above. You would only modify the query to have the necessary WHERE clause in it.
0
 
earth man2Commented:
create a temporary table using select query on big table with some where clause to limit no rows off.
0
 
enggAuthor Commented:
Thank you.
I can't use dblink as well, because it's not there in the Functions in pgAdmin III.
I think I will have to download 'contrib' directory from the Internet.
Please let me know if you know where to download it from.
Thanks.
0
 
ivanovnCommented:
If you have the latest version of PostgreSQL, then download the binaries at http://www.postgresql.org/ftp/binary/v8.1.4/win32/ and there will be a contrib directory there with all the necessary functions.

If you aren't running the latest version, just navigate a few levesl up from that link (http://www.postgresql.org/ftp/binary/) and select the desired version.
0
 
enggAuthor Commented:
Thank you.
0
All Courses

From novice to tech pro — start learning today.