I am trying to copy an entire table from one database to another in Postgres. Any suggestions?
+5
A:
Use pg_dump to dump table data, and then restore it with psql.
Pablo Santa Cruz
2010-07-07 13:29:34
I tried using pg_dump but I get "Access is denied".
nix
2010-07-07 13:39:43
Then use another databaserole to connect, a role that has enough permissions. http://www.postgresql.org/docs/8.4/static/app-pgdump.html
Frank Heikens
2010-07-07 13:43:15
What am I doing wrong?pg_dump -t "tablename" dbName --role "postgres" > db.sql"postgres" would be the user I'm trying to set the role to. It still gives me "Access is denied".
nix
2010-07-07 14:48:39
Do you have permissions to write the db.sql file?
pcent
2010-07-07 16:29:43
How do I check what permissions I have?
nix
2010-07-07 17:27:55
A:
Using dblink would be more convenient!
truncate table tableA;
insert into tableA
select *
from dblink('dbname=postgres hostaddr=xxx.xxx.xxx.xxx dbname=mydb user=postgres',
'select a,b from tableA')
as t1(a text,b text);
tinychen
2010-07-08 01:45:17