Solved

copy table from one database to another

Posted on 2006-06-28
11
23,219 Views
Last Modified: 2011-08-18
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.
0
Comment
Question by:engg
  • 4
  • 4
  • 3
11 Comments
 
LVL 22

Assisted Solution

by:earth man2
earth man2 earned 250 total points
ID: 17007984
0
 

Author Comment

by:engg
ID: 17009741
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
 
LVL 10

Expert Comment

by:ivanovn
ID: 17009841
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
 
LVL 10

Accepted Solution

by:
ivanovn earned 250 total points
ID: 17009936
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
 
LVL 22

Expert Comment

by:earth man2
ID: 17016969
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:engg
ID: 17017680
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
 
LVL 10

Expert Comment

by:ivanovn
ID: 17017716
Yes you could use the dblink feature described above. You would only modify the query to have the necessary WHERE clause in it.
0
 
LVL 22

Expert Comment

by:earth man2
ID: 17027704
create a temporary table using select query on big table with some where clause to limit no rows off.
0
 

Author Comment

by:engg
ID: 17083427
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
 
LVL 10

Expert Comment

by:ivanovn
ID: 17087213
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
 

Author Comment

by:engg
ID: 17338355
Thank you.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now