pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBackup?
10+ messages / 8 participants
[nested] [flat]
* Backup?
@ 2000-06-27 13:09 Emils Klotins <emils@dot.lv>
0 siblings, 2 replies; 10+ messages in thread
From: Emils Klotins @ 2000-06-27 13:09 UTC (permalink / raw)
To: pgsql-sql
Hello,
I had a look for 'backup' on the mailing list archives and strangely
enough didn't find a message containing the word in admin, general
and sql mailing lists... I am sure this must have been asked before,
but still:
Would it be possible to backup a db by just copying the dir struct
under the PG_DATA or must I absolutely use pg_dump? I am
using Postgres 7.0. The purpose of the backup is of course to have
a copy handy if eg the harddrive breaks. As I understand pg_dump
could be comparatively slower, plus it produces a text format
dumpfile, which could be kind of large in my db in future.
Thanks in advance,
Emils
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Backup?
@ 2000-06-27 14:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Emils Klotins <emils@dot.lv>
1 sibling, 0 replies; 10+ messages in thread
From: Tom Lane @ 2000-06-27 14:43 UTC (permalink / raw)
To: Emils Klotins <emils@dot.lv>; +Cc: pgsql-sql
"Emils Klotins" <emils@dot.lv> writes:
> Would it be possible to backup a db by just copying the dir struct
> under the PG_DATA or must I absolutely use pg_dump?
If you ensure nothing at all is happening (as in "shut down the
postmaster" --- I would not trust any half measures) then a dump of
PGDATA would work. If anything is going on then a dump done that way
will be inconsistent.
> As I understand pg_dump could be comparatively slower, plus it
> produces a text format dumpfile, which could be kind of large in my db
> in future.
pg_dump is slower, but since it can run in parallel with other
activities, I'm not sure that the extra time is a material
disadvantage. I wouldn't assume that the text dumpfile will be
larger than a tar of the database directory either --- very likely
it'd be the other way round. Finally, the text dump is amenable to
editing, selective restoration, etc, which you *cannot* do with
a directory dump --- the only thing a directory dump is good for is
restoring the entire DB to exactly where it was.
The main thing that makes a directory dump less useful than it might
appear to be is that the logical state of each table is split between
the table file proper (containing tuples marked with transaction
insert/delete stamps) and pg_log (which tells you which transactions
committed and which did not). Thus you cannot restore just one
table file; you have to restore pg_log as well, and that makes it
an all-or-nothing proposition.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Backup?
@ 2000-06-27 16:17 Bryan White <bryan@arcamax.com>
parent: Emils Klotins <emils@dot.lv>
1 sibling, 0 replies; 10+ messages in thread
From: Bryan White @ 2000-06-27 16:17 UTC (permalink / raw)
To: Emils Klotins <emils@dot.lv>; pgsql-sql
> Hello,
>
> I had a look for 'backup' on the mailing list archives and strangely
> enough didn't find a message containing the word in admin, general
> and sql mailing lists... I am sure this must have been asked before,
> but still:
>
> Would it be possible to backup a db by just copying the dir struct
> under the PG_DATA or must I absolutely use pg_dump? I am
> using Postgres 7.0. The purpose of the backup is of course to have
> a copy handy if eg the harddrive breaks. As I understand pg_dump
> could be comparatively slower, plus it produces a text format
> dumpfile, which could be kind of large in my db in future.
The pg_dump will in general be smaller. There is a fair amount of overhead
in fitting rows into page boundaries. Also the indexes are not backed up.
Here are some datapoints from my system. I have a datadirectory that is
2.4GB. A pg_dump from that is 800MB (about 400MB compressed). My nightly
dump currently takes about 10 minutes during which the database is not down.
A reload takes 100 minutes including a vacuum analyze. I also sleep better
knowing the data is in a readable text format that I could work with if I
had to.
^ permalink raw reply [nested|flat] 10+ messages in thread
* backup
@ 2009-06-09 09:53 Jyoti Seth <jyotiseth2001@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Jyoti Seth @ 2009-06-09 09:53 UTC (permalink / raw)
To: pgsql-sql
Hi all,
Can we take backup of specific data of a table (using where clause)?
Thanks,
Jyoti
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: backup
@ 2009-06-09 10:02 Ashish Karalkar <ashish.karalkar@enterprisedb.com>
parent: Jyoti Seth <jyotiseth2001@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Ashish Karalkar @ 2009-06-09 10:02 UTC (permalink / raw)
To: Jyoti Seth <jyotiseth2001@gmail.com>; +Cc: pgsql-sql
Jyoti Seth wrote:
>
> Hi all,
>
>
>
> Can we take backup of specific data of a table (using where clause)?
>
>
>
> Thanks,
>
> Jyoti
>
>
>
Try Selective COPY
With Regards
Ashish Karalkar
EnterpriseDB
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: backup
@ 2009-06-09 12:33 Jasen Betts <jasen@xnet.co.nz>
parent: Jyoti Seth <jyotiseth2001@gmail.com>
1 sibling, 2 replies; 10+ messages in thread
From: Jasen Betts @ 2009-06-09 12:33 UTC (permalink / raw)
To: pgsql-sql
On 2009-06-09, Jyoti Seth <jyotiseth2001@gmail.com> wrote:
> This is a multipart message in MIME format.
>
> ------=_NextPart_000_003C_01C9E916.43A8D460
> Content-Type: text/plain;
> charset="us-ascii"
> Content-Transfer-Encoding: 7bit
>
> Hi all,
>
>
>
> Can we take backup of specific data of a table (using where clause)?
in psql:
/copy (select * from table WHERE condition ) to 'FILENAME'
requires postgres version >= 8.2 IIRC.
you can use any query.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: backup
@ 2009-06-10 05:08 Jyoti Seth <jyotiseth2001@gmail.com>
parent: Jasen Betts <jasen@xnet.co.nz>
1 sibling, 2 replies; 10+ messages in thread
From: Jyoti Seth @ 2009-06-10 05:08 UTC (permalink / raw)
To: 'Jasen Betts' <jasen@xnet.co.nz>; pgsql-sql
I tried using /copy command. It is not giving any error but also not
creating any file.
Thanks
Jyoti
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Jasen Betts
Sent: Tuesday, June 09, 2009 6:03 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] backup
On 2009-06-09, Jyoti Seth <jyotiseth2001@gmail.com> wrote:
> This is a multipart message in MIME format.
>
> ------=_NextPart_000_003C_01C9E916.43A8D460
> Content-Type: text/plain;
> charset="us-ascii"
> Content-Transfer-Encoding: 7bit
>
> Hi all,
>
>
>
> Can we take backup of specific data of a table (using where clause)?
in psql:
/copy (select * from table WHERE condition ) to 'FILENAME'
requires postgres version >= 8.2 IIRC.
you can use any query.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: backup
@ 2009-06-10 05:46 Jyoti Seth <jyotiseth2001@gmail.com>
parent: Jasen Betts <jasen@xnet.co.nz>
1 sibling, 0 replies; 10+ messages in thread
From: Jyoti Seth @ 2009-06-10 05:46 UTC (permalink / raw)
To: 'Jasen Betts' <jasen@xnet.co.nz>; pgsql-sql
Thanks it worked when I changed the command '/copy' to '\copy'
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Jasen Betts
Sent: Tuesday, June 09, 2009 6:03 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] backup
On 2009-06-09, Jyoti Seth <jyotiseth2001@gmail.com> wrote:
> This is a multipart message in MIME format.
>
> ------=_NextPart_000_003C_01C9E916.43A8D460
> Content-Type: text/plain;
> charset="us-ascii"
> Content-Transfer-Encoding: 7bit
>
> Hi all,
>
>
>
> Can we take backup of specific data of a table (using where clause)?
in psql:
/copy (select * from table WHERE condition ) to 'FILENAME'
requires postgres version >= 8.2 IIRC.
you can use any query.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: backup
@ 2009-06-10 05:50 Gurjeet Singh <singh.gurjeet@gmail.com>
parent: Jyoti Seth <jyotiseth2001@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Gurjeet Singh @ 2009-06-10 05:50 UTC (permalink / raw)
To: Jyoti Seth <jyotiseth2001@gmail.com>; +Cc: Jasen Betts <jasen@xnet.co.nz>; pgsql-sql
It should be \copy . This is a psql command; if you have direct access to
database host machine, you might want to use the SQL command COPY for it's
performance.
http://www.postgresql.org/docs/8.3/interactive/app-psql.html
http://www.postgresql.org/docs/8.3/interactive/sql-copy.html
Best regards,
On Wed, Jun 10, 2009 at 10:38 AM, Jyoti Seth <jyotiseth2001@gmail.com>wrote:
> I tried using /copy command. It is not giving any error but also not
> creating any file.
>
> Thanks
> Jyoti
>
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:
> pgsql-sql-owner@postgresql.org]
> On Behalf Of Jasen Betts
> Sent: Tuesday, June 09, 2009 6:03 PM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] backup
>
> On 2009-06-09, Jyoti Seth <jyotiseth2001@gmail.com> wrote:
> > This is a multipart message in MIME format.
> >
> > ------=_NextPart_000_003C_01C9E916.43A8D460
> > Content-Type: text/plain;
> > charset="us-ascii"
> > Content-Transfer-Encoding: 7bit
> >
> > Hi all,
> >
> >
> >
> > Can we take backup of specific data of a table (using where clause)?
>
> in psql:
>
> /copy (select * from table WHERE condition ) to 'FILENAME'
>
> requires postgres version >= 8.2 IIRC.
>
> you can use any query.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Lets call it Postgres
EnterpriseDB http://www.enterprisedb.com
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
Mail sent from my BlackLaptop device
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: backup
@ 2009-06-10 06:02 Abbas <abbas.dba@gmail.com>
parent: Jyoti Seth <jyotiseth2001@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Abbas @ 2009-06-10 06:02 UTC (permalink / raw)
To: Jyoti Seth <jyotiseth2001@gmail.com>; +Cc: Jasen Betts <jasen@xnet.co.nz>; pgsql-sql
On Wed, Jun 10, 2009 at 10:38 AM, Jyoti Seth <jyotiseth2001@gmail.com>wrote:
> I tried using /copy command. It is not giving any error but also not
> creating any file.
> Thanks
> Jyoti
Use copy command as follows if the OS is Linux,
postgres=# copy (select * from test where setid>20) to '/var/tmp/t2.dmp';
COPY 3
postgres=# \q
-bash-3.1$ cat /var/tmp/t2.dmp
1234 22 1 2009-01-08 00:00:00 dup1-2
9876 21 1 2009-01-10 00:00:00 dup2-1
1 21 1 2009-01-10 00:00:00 dup2-1
If it is Windows, use can use as,
postgres=# copy (select *from test where a<6) to 'C:/Program
Files/PostgreSQL/8.3/data/samp.sql';
COPY 9
Thanks,
Abbas
EnterpriseDB
>
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:
> pgsql-sql-owner@postgresql.org]
> On Behalf Of Jasen Betts
> Sent: Tuesday, June 09, 2009 6:03 PM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] backup
>
> On 2009-06-09, Jyoti Seth <jyotiseth2001@gmail.com> wrote:
> > This is a multipart message in MIME format.
> >
> > ------=_NextPart_000_003C_01C9E916.43A8D460
> > Content-Type: text/plain;
> > charset="us-ascii"
> > Content-Transfer-Encoding: 7bit
> >
> > Hi all,
> >
> >
> >
> > Can we take backup of specific data of a table (using where clause)?
>
> in psql:
>
> /copy (select * from table WHERE condition ) to 'FILENAME'
>
> requires postgres version >= 8.2 IIRC.
>
> you can use any query.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2009-06-10 06:02 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-06-27 13:09 Backup? Emils Klotins <emils@dot.lv>
2000-06-27 14:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-06-27 16:17 ` Bryan White <bryan@arcamax.com>
2009-06-09 09:53 backup Jyoti Seth <jyotiseth2001@gmail.com>
2009-06-09 10:02 ` Re: backup Ashish Karalkar <ashish.karalkar@enterprisedb.com>
2009-06-09 12:33 ` Re: backup Jasen Betts <jasen@xnet.co.nz>
2009-06-10 05:08 ` Re: backup Jyoti Seth <jyotiseth2001@gmail.com>
2009-06-10 05:50 ` Re: backup Gurjeet Singh <singh.gurjeet@gmail.com>
2009-06-10 06:02 ` Re: backup Abbas <abbas.dba@gmail.com>
2009-06-10 05:46 ` Re: backup Jyoti Seth <jyotiseth2001@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox