[GENERAL] Need help on how to backup a table
Hi all, I am trying to backup a large table with about 6 million rows. I want to export the data from the table and be able to import it into another table on a different database server (from pgsql 8.1 to 8.2). I need to export the data through SQL query 'cause I want to do a gradual backup. Does pgsql have a facility for this? Thanks in advance for your reply. Victor -- View this message in context: http://www.nabble.com/Need-help-on-how-to-backup-a-table-tp16346381p16346381.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on how to backup a table
On Thu, Mar 27, 2008 at 11:05 PM, ajcity [EMAIL PROTECTED] wrote: Hi all, I am trying to backup a large table with about 6 million rows. I want to export the data from the table and be able to import it into another table on a different database server (from pgsql 8.1 to 8.2). I need to export the data through SQL query 'cause I want to do a gradual backup. Does pgsql have a facility for this? Thanks in advance for your reply. Have you looked at pg_dump -t http://www.postgresql.org/docs/8.2/static/app-pgdump.html Joey
Re: [GENERAL] Need help on how to backup a table
CAJ CAJ wrote: Have you looked at pg_dump -t http://www.postgresql.org/docs/8.2/static/app-pgdump.html Joey Thanks for quick response but pg_dump does not allow me to dump from a SQL SELECT query which is what I wanna do. -- View this message in context: http://www.nabble.com/Need-help-on-how-to-backup-a-table-tp16346381p16346814.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on how to backup a table
ajcity wrote: CAJ CAJ wrote: Have you looked at pg_dump -t http://www.postgresql.org/docs/8.2/static/app-pgdump.html Joey Thanks for quick response but pg_dump does not allow me to dump from a SQL SELECT query which is what I wanna do. May be selective COPY will help you. http://www.postgresql.org/docs/8.3/interactive/sql-copy.html With regards Ashish === sms START NETCORE to 575758 to get updates on Netcore's enterprise products and services sms START MYTODAY to 09845398453 for more information on our mobile consumer services or go to http://www.mytodaysms.com ===
Re: [GENERAL] Need help on how to backup a table
ajcity wrote: Thanks all. The COPY command seems to do the work. One more thing, say I want the data dumped on a remote machine rather than on the current machine, how would I do that without having to first dump it on the local machine then uploading to the remote machine? Install psql client on that other m/c and fire command psql -c copy from there :) ofcourse with proper pg_hba.conf With regards Ashish === sms START NETCORE to 575758 to get updates on Netcore's enterprise products and services sms START MYTODAY to 09845398453 for more information on our mobile consumer services or go to http://www.mytodaysms.com === -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on how to backup a table
Thanks all. The COPY command seems to do the work. One more thing, say I want the data dumped on a remote machine rather than on the current machine, how would I do that without having to first dump it on the local machine then uploading to the remote machine? -- View this message in context: http://www.nabble.com/Need-help-on-how-to-backup-a-table-tp16346381p16347825.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on how to backup a table
If I wanted to use that with a command like COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO 'filename'; do I specify the file location for the remote machine as the filename or do I specify the location for local machine? And what if the psql clients are different (local: 8.1.5 remote:8.2.6)? Thanks Victor -- View this message in context: http://www.nabble.com/Need-help-on-how-to-backup-a-table-tp16346381p16348299.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on how to backup a table
am Fri, dem 28.03.2008, um 2:08:17 -0700 mailte ajcity folgendes: If I wanted to use that with a command like COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO 'filename'; do I specify the file location for the remote machine as the filename or do I specify the location for local machine? Local file systems, and the user postgres needs write-access. I'm using /tmp/... for such. And yes: COPY from a select works only for 8.2 up, not for 8.1. Create a table via 'create table as select ...' and COPY this table. And what if the psql clients are different (local: 8.1.5 remote:8.2.6)? no matter Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on how to backup a table
ajcity wrote: If I wanted to use that with a command like COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO 'filename'; do I specify the file location for the remote machine as the filename or do I specify the location for local machine? And what if the psql clients are different (local: 8.1.5 remote:8.2.6)? Thanks Victor from remote m/c psql -h some_hostname -c copy temp to stdout -Upostgres postgres temp.txt Will create temp file on the m/c from which u r firing the command . With regards Ashish === sms START NETCORE to 575758 to get updates on Netcore's enterprise products and services sms START MYTODAY to 09845398453 for more information on our mobile consumer services or go to http://www.mytodaysms.com === -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on how to backup a table
Local file systems, and the user postgres needs write-access. I'm using /tmp/... for such. And yes: COPY from a select works only for 8.2 up, not for 8.1. Create a table via 'create table as select ...' and COPY this table. I'm trying to avoid exporting to the local machine before uploading to the remote machine; I wanna just run it from the remote machine and have the data on the remote machine once its done. Is there a way to do this? -- View this message in context: http://www.nabble.com/Need-help-on-how-to-backup-a-table-tp16346381p16349003.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on how to backup a table
am Fri, dem 28.03.2008, um 3:01:43 -0700 mailte ajcity folgendes: Local file systems, and the user postgres needs write-access. I'm using /tmp/... for such. And yes: COPY from a select works only for 8.2 up, not for 8.1. Create a table via 'create table as select ...' and COPY this table. I'm trying to avoid exporting to the local machine before uploading to the remote machine; I wanna just run it from the remote machine and have the data on the remote machine once its done. Is there a way to do this? Without quote-sign it's hard to understand, who has written what. Okay, you can mount the remote-filesystem over the network on the server, is this a option for you? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on how to backup a table
Hi all, I am trying to backup a large table with about 6 million rows. I want to export the data from the table and be able to import it into another table on a different database server (from pgsql 8.1 to 8.2). I need to export the data through SQL query 'cause I want to do a gradual backup. Does pgsql have a facility for this? Thanks in advance for your reply. Here's an easy solution: psql -c COPY command here dbname | ssh [EMAIL PROTECTED] dd of=/path/tbl.backup You can run this from your local server, and immediately pipe it over a secure shell to the other server, and write it to a file there. The /path/ you specify is local to the remote server. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need help on how to backup a table
ashish-21 wrote: ajcity wrote: If I wanted to use that with a command like COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO 'filename'; do I specify the file location for the remote machine as the filename or do I specify the location for local machine? And what if the psql clients are different (local: 8.1.5 remote:8.2.6)? Thanks Victor from remote m/c psql -h some_hostname -c copy temp to stdout -Upostgres postgres temp.txt Will create temp file on the m/c from which u r firing the command . With regards Ashish Thanks all I tried this option and it worked PERFECTLY. Thanks every body. Victor -- View this message in context: http://www.nabble.com/Need-help-on-how-to-backup-a-table-tp16346381p16352647.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
