Обсуждение: Reliably backing up a live database
Hello, I am relatively new to SQL databases in general, and very new to postgresql, so please be gentle. My question has to do with how to properly perform a dump on a live database, that I've sort of inherited. We have some developers that are assisting with making some modifications, but I want to make sure that something they told me is true... I was told by one of the developers (who admits that he is still learning postgres) that I could get a successful dump of the live database using the following command, and more importantly, that I could do this safely without stopping the database first: ./pg_dump -U postgres -Z --blobs --oids --encoding=UTF-8 dbname > dbname.sql.gz First, will that command result in a dump file that can be used to perform a full restore in event of a catastrophe (these will be uploaded to off-site storage)? Second, can this command be run safely on a running database, or should the database be stopped first? If the latter, would someone be so kind as to provide an example of the commands necessary to stop this database, perform the dump, then restart it, that I could put in a cron job? Thanks in advance for any help... Simon
On Fri, February 24, 2012 9:07 am, Tanstaafl wrote: > Hello, > > I am relatively new to SQL databases in general, and very new to > postgresql, so please be gentle. > > My question has to do with how to properly perform a dump on a live > database, that I've sort of inherited. We have some developers that are > assisting with making some modifications, but I want to make sure that > something they told me is true... > > I was told by one of the developers (who admits that he is still > learning postgres) that I could get a successful dump of the live > database using the following command, and more importantly, that I could > do this safely without stopping the database first: > > ./pg_dump -U postgres -Z --blobs --oids --encoding=UTF-8 dbname > > dbname.sql.gz > > First, will that command result in a dump file that can be used to > perform a full restore in event of a catastrophe (these will be uploaded > to off-site storage)? > > Second, can this command be run safely on a running database, or should > the database be stopped first? If the latter, would someone be so kind > as to provide an example of the commands necessary to stop this > database, perform the dump, then restart it, that I could put in a cron > job? I might argue about the need/desirability of a couple of the switches, (--oids in particular: If you need it, you probably should redesign your database so you don't) but that depends on your environment. But yes, that should preform a full backup on 'dbname' while you are running the database. (In fact, I think you have to be running the database to run pg_dump.) Of course, the best way to be sure the above meets your needs is to set up a test server and restore the database to it: a 'tested to work in practice' backup/restore process beats a 'works in theory' backup/restore process any day of the week. Daniel T. Staal --------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. ---------------------------------------------------------------
Tanstaafl <tanstaafl@libertytrek.org> writes:
> I was told by one of the developers (who admits that he is still
> learning postgres) that I could get a successful dump of the live
> database using the following command, and more importantly, that I could
> do this safely without stopping the database first:
> ./pg_dump -U postgres -Z --blobs --oids --encoding=UTF-8 dbname >
> dbname.sql.gz
> First, will that command result in a dump file that can be used to
> perform a full restore in event of a catastrophe (these will be uploaded
> to off-site storage)?
This will only get you the content of the single database "dbname";
a Postgres installation ("cluster") can contain multiple databases.
Also, cluster-wide entities such as role definitions don't get dumped
this way. For most purposes you want to use pg_dumpall for routine
backup purposes, as that fixes both these issues.
Also, in any modern version of PG, --blobs is a no-op (it's on by
default) and --oids is deprecated.
> Second, can this command be run safely on a running database, or should
> the database be stopped first?
Nobody stops the database for this. pg_dump is built to get a
consistent snapshot despite concurrent updates. There are some
limitations as to doing schema changes (DDL) concurrently, but ordinary
applications don't often fall foul of that.
Depending on what you're doing, there are other backup methods besides
periodic pg_dump that might be superior. It'd be worth your while to
read the fine manual:
http://www.postgresql.org/docs/9.1/static/backup.html
(adjust link depending on which PG version you're running,
as the facilities vary over time)
regards, tom lane
Thanks very much for your responses Tom (and Daniel)... I will get with
our developers on this. I believe that the --oids switch is being used
because this is a very old database that has a web front end and
originally used postgreSQL 7.x.
We will doing a full rewrite of it soon, so unless the requirement to
use --oids can be fixed easily, we probably won't worry about that for now.
Thanks again,
Simon
On 2012-02-24 11:31 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Tanstaafl<tanstaafl@libertytrek.org> writes:
>> I was told by one of the developers (who admits that he is still
>> learning postgres) that I could get a successful dump of the live
>> database using the following command, and more importantly, that I could
>> do this safely without stopping the database first:
>
>> ./pg_dump -U postgres -Z --blobs --oids --encoding=UTF-8 dbname>
>> dbname.sql.gz
>
>> First, will that command result in a dump file that can be used to
>> perform a full restore in event of a catastrophe (these will be uploaded
>> to off-site storage)?
>
> This will only get you the content of the single database "dbname";
> a Postgres installation ("cluster") can contain multiple databases.
> Also, cluster-wide entities such as role definitions don't get dumped
> this way. For most purposes you want to use pg_dumpall for routine
> backup purposes, as that fixes both these issues.
>
> Also, in any modern version of PG, --blobs is a no-op (it's on by
> default) and --oids is deprecated.
>
>> Second, can this command be run safely on a running database, or should
>> the database be stopped first?
>
> Nobody stops the database for this. pg_dump is built to get a
> consistent snapshot despite concurrent updates. There are some
> limitations as to doing schema changes (DDL) concurrently, but ordinary
> applications don't often fall foul of that.
>
> Depending on what you're doing, there are other backup methods besides
> periodic pg_dump that might be superior. It'd be worth your while to
> read the fine manual:
> http://www.postgresql.org/docs/9.1/static/backup.html
> (adjust link depending on which PG version you're running,
> as the facilities vary over time)
>
> regards, tom lane
>
On 2012-02-24 11:31 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Also, in any modern version of PG, --blobs is a no-op (it's on by > default) and --oids is deprecated. Ok, coming back to this - I actually need to restore a copy of my DB. I've been using the command: pg_dumpall --username=username -o -f mydb_backup.sql.gz Just for fun, I also just stopped pg and did: tar -pvczf pg91_data.tar.gz /var/lib/postgresql/9.1/data So, first question... More out of curiosity than anything - on a system that is already running the exact same DB, only it contains outdated data, can I simply stop pg on the target server I want to update, then rm -r /var/lib/postgresql/9.1/data, then tar -xvczf data.tar.gz /var/lib/postgresql/9.1/data ? If this won't work or is not recommended, what is the proper command to restore this db dump file to a fully functional postgresql server that is already running an older copy of the exact same DB? Is it: psql -f mydb_backup.sql.gz postgres ? Sorry for the newbie question, still trying to wrap my head around the differences between mysql and postgresql...
On 2013-04-15 5:36 PM, Tanstaafl <tanstaafl@libertytrek.org> wrote: > I've been using the command: > > pg_dumpall --username=username -o -f mydb_backup.sql.gz Ok, please correct me... I had thought the above command would result in a gzipped database dump, due to the .gz extension. Obviously, this was a dumb assumption. I just tried to extract it, and got an error, but renaming the file to remove the .gz at the end (so it is just .sql) gives me a text file that loads just fine and I can see all the DB data, so obviously the file is not .gz compressed. Reading man pg_dump lead me to believe that I needed to add the -Fc flag to the above command - but, I don't see anything about what type of compression it uses... will it be .gz? How do I specify (ie, .gz, .gz2 But... Trying the above failed with an invalid option, an a quick man pg_dumpall reveals that there is no -F option... So, is it possible to get a gzipped (or otherwise compressed) dump using pg_dumpall?
On 12/31/2013 5:40 AM, Tanstaafl wrote: > On 2013-04-15 5:36 PM, Tanstaafl <tanstaafl@libertytrek.org> wrote: >> I've been using the command: >> >> pg_dumpall --username=username -o -f mydb_backup.sql.gz > > Ok, please correct me... > > I had thought the above command would result in a gzipped database > dump, due to the .gz extension. > > Obviously, this was a dumb assumption. > > I just tried to extract it, and got an error, but renaming the file to > remove the .gz at the end (so it is just .sql) gives me a text file > that loads just fine and I can see all the DB data, so obviously the > file is not .gz compressed. > > Reading man pg_dump lead me to believe that I needed to add the -Fc > flag to the above command - but, I don't see anything about what type > of compression it uses... will it be .gz? How do I specify (ie, .gz, .gz2 > > But... > > Trying the above failed with an invalid option, an a quick man > pg_dumpall reveals that there is no -F option... > > So, is it possible to get a gzipped (or otherwise compressed) dump > using pg_dumpall? > > I believe you have to use pg_dumpall --username=username -o | gzip > mydb_backup.sql.gz
On 2013-12-31 10:16 AM, Ken Benson <ken@infowerks.com> wrote: > On 12/31/2013 5:40 AM, Tanstaafl wrote: >> So, is it possible to get a gzipped (or otherwise compressed) dump >> using pg_dumpall? > I believe you have to use > pg_dumpall --username=username -o | gzip > mydb_backup.sql.gz Thanks Ken, googling revealed this solution and I was just getting ready to reply if this was the only/best way...