Thread: 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.

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.

  

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.
 

===================================================================

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

===================================================================

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.

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

===================================================================

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.

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

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

===================================================================

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.

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

>  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 user@host.net "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.

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.