Re: Reliably backing up a live database

On 2012-02-24 11:31 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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...