Re: [GENERAL] File system level copy
Hao Wang wrote:
I installed PostgresSQL-8.3 on my linux machine.
The cluster directory is /usr/local/data and I created three
databases
named db1, db2, and db3. db1 is
in the default tablespace 'pg_default'. db2 is in
'/home/tablespace/space1/' and db3 is in
'/home/tablespace/space2/'. I want to copy the cluster directory
and
the db3 tablespace
folder('/home/tablespace/space2/') without stopping the database
server. Then I want to use the
cluster directory and db3's tablespace in another linux machine to
recover 'db3' database. Does this
way work? If not, why?
First, you need a correct backup for recovery.
Before copying, run pg_start_backup, and pg_stop_backup afterwards.
Then you need to have recovery.conf and WAL archives (or be lucky and
all WALs are still in pg_xlog).
WAL contains changes to all databases in the cluster, so you cannot
recover only one database, you'll
have to recover them all.
Read
http://www.postgresql.org/docs/current/static/continuous-archiving.html
for background and details.
This is PITR, right?
I don't want to use this way because I'm not allowed to change the
configuration
parameter of database server. I just want to use some whole DB copy to
restore
db3 in another machine. And I don't want to use pg_dump because I
think db3
is so large that pg_dump will probably have bad performance.
That's a whole lot of arbitrary restrictions.
If all you want is a copy of the database, pg_dump is what
you should use. Besides, it is the only way to get a copy
of just one database. What's the problem if pg_dump takes
a few hours or days (I don't know how big you DB is)?
A side thought: if the DB is not configured for PITR and
pg_dump takes too long, how do you perform your backups?
Yours,
Laurenz Albe
--
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] File system level copy
My purpose is not to do backup for my database. I just want to copy the whole
db3 database to another machine and restore it. That database could be very
large so I think directly copy is more efficient than pg_dump. So I'd like to
do some test to see if this way works. If it doesn't work, I will consider to
use pg_dump.
Thank you for your feedback.
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Albe Laurenz
Sent: Thursday, November 15, 2012 4:52 PM
To: Wang, Hao; pgsql-general@postgresql.org
Subject: Re: [GENERAL] File system level copy
Hao Wang wrote:
I installed PostgresSQL-8.3 on my linux machine.
The cluster directory is /usr/local/data and I created three
databases
named db1, db2, and db3. db1 is
in the default tablespace 'pg_default'. db2 is in
'/home/tablespace/space1/' and db3 is in '/home/tablespace/space2/'.
I want to copy the cluster directory
and
the db3 tablespace
folder('/home/tablespace/space2/') without stopping the database
server. Then I want to use the cluster directory and db3's
tablespace in another linux machine to recover 'db3' database. Does
this way work? If not, why?
First, you need a correct backup for recovery.
Before copying, run pg_start_backup, and pg_stop_backup afterwards.
Then you need to have recovery.conf and WAL archives (or be lucky and
all WALs are still in pg_xlog).
WAL contains changes to all databases in the cluster, so you cannot
recover only one database, you'll
have to recover them all.
Read
http://www.postgresql.org/docs/current/static/continuous-archiving.html
for background and details.
This is PITR, right?
I don't want to use this way because I'm not allowed to change the
configuration
parameter of database server. I just want to use some whole DB copy to
restore
db3 in another machine. And I don't want to use pg_dump because I
think db3
is so large that pg_dump will probably have bad performance.
That's a whole lot of arbitrary restrictions.
If all you want is a copy of the database, pg_dump is what you should use.
Besides, it is the only way to get a copy of just one database. What's the
problem if pg_dump takes a few hours or days (I don't know how big you DB is)?
A side thought: if the DB is not configured for PITR and pg_dump takes too
long, how do you perform your backups?
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
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] File system level copy
Hao Wang wrote: My purpose is not to do backup for my database. I understood that. It was just a side comment. I just want to copy the whole db3 database to another machine and restore it. That database could be very large so I think directly copy is more efficient than pg_dump. So I'd like to do some test to see if this way works. If it doesn't work, I will consider to use pg_dump. Any attempt to make a file system copy of a live system without doing PITR will very likely cause data corruption. You'll have to use pg_dump. Yours, Laurenz Albe -- 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] File system level copy
Hao Wang wrote:
I installed PostgresSQL-8.3 on my linux machine.
The cluster directory is /usr/local/data and I created three databases
named db1, db2, and db3. db1 is
in the default tablespace 'pg_default'. db2 is in
'/home/tablespace/space1/' and db3 is in
'/home/tablespace/space2/'. I want to copy the cluster directory and
the db3 tablespace
folder('/home/tablespace/space2/') without stopping the database
server. Then I want to use the
cluster directory and db3's tablespace in another linux machine to
recover 'db3' database. Does this
way work? If not, why?
First, you need a correct backup for recovery.
Before copying, run pg_start_backup, and pg_stop_backup afterwards.
Then you need to have recovery.conf and WAL archives
(or be lucky and all WALs are still in pg_xlog).
WAL contains changes to all databases in the cluster, so
you cannot recover only one database, you'll have to
recover them all.
Read
http://www.postgresql.org/docs/current/static/continuous-archiving.html
for background and details.
Yours,
Laurenz Albe
--
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] File system level copy
This is PITR, right?
I don't want to use this way because I'm not allowed to change the
configuration parameter of database server. I just want to use some whole DB
copy to restore db3 in another machine. And I don't want to use pg_dump because
I think db3 is so large that pg_dump will probably have bad performance.
-Original Message-
From: Albe Laurenz [mailto:laurenz.a...@wien.gv.at]
Sent: Wednesday, November 14, 2012 6:49 PM
To: Wang, Hao; pgsql-general@postgresql.org
Subject: RE: [GENERAL] File system level copy
Hao Wang wrote:
I installed PostgresSQL-8.3 on my linux machine.
The cluster directory is /usr/local/data and I created three databases
named db1, db2, and db3. db1 is
in the default tablespace 'pg_default'. db2 is in
'/home/tablespace/space1/' and db3 is in
'/home/tablespace/space2/'. I want to copy the cluster directory and
the db3 tablespace
folder('/home/tablespace/space2/') without stopping the database
server. Then I want to use the
cluster directory and db3's tablespace in another linux machine to
recover 'db3' database. Does this
way work? If not, why?
First, you need a correct backup for recovery.
Before copying, run pg_start_backup, and pg_stop_backup afterwards.
Then you need to have recovery.conf and WAL archives (or be lucky and all WALs
are still in pg_xlog).
WAL contains changes to all databases in the cluster, so you cannot recover
only one database, you'll have to recover them all.
Read
http://www.postgresql.org/docs/current/static/continuous-archiving.html
for background and details.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
