Re: [GENERAL] Using cp to back up a database?

2017-10-10 Thread Stephen Frost
Ron, * Ron Johnson (ron.l.john...@cox.net) wrote: > Maybe my original question wasn't clear, so I'll try again: is it > safe to do a physical using cp (as opposed to rsync)? Frankly, I'd say no. There's nothing to guarantee that the backup is actually sync'd out to disk. Further, you're

Re: [GENERAL] Using cp to back up a database?

2017-10-09 Thread Ron Johnson
On 10/09/2017 01:02 PM, Scott Mead wrote: On Mon, Oct 9, 2017 at 1:19 PM, Ron Johnson > wrote: Maybe my original question wasn't clear, so I'll try again: is it safe to do a physical using cp (as opposed to rsync)? Yes --

Re: [GENERAL] Using cp to back up a database?

2017-10-09 Thread Scott Mead
On Mon, Oct 9, 2017 at 1:19 PM, Ron Johnson wrote: > Maybe my original question wasn't clear, so I'll try again: is it safe to > do a physical using cp (as opposed to rsync)? > Yes -- however* you must configure WAL archiving* first. If not, no backup tool, cp, rsync,

Re: [GENERAL] Using cp to back up a database?

2017-10-09 Thread Ron Johnson
Maybe my original question wasn't clear, so I'll try again: is it safe to do a physical using cp (as opposed to rsync)? On 10/09/2017 11:49 AM, Darren Douglas wrote: Ron: Here is an explanation that may help a bit. Your script is executing a PHYSICAL backup. A physical backup is simply a

Re: [GENERAL] Using cp to back up a database?

2017-10-09 Thread Darren Douglas
Sorry, forgot about your main question about cp ... So, because the physical backup is a copy of the entire cluster data directory, ANY valid and safe method for copying that directory is ok. In most production installations, that means that an enterprise backup tool may be used to accomplish

Re: [GENERAL] Using cp to back up a database?

2017-10-09 Thread Jeff Janes
On Mon, Oct 9, 2017 at 9:41 AM, Ron Johnson wrote: > On 10/09/2017 11:33 AM, Jeff Janes wrote: > > On Mon, Oct 9, 2017 at 6:40 AM, Ron Johnson wrote: > >> Hi, >> >> v8.4.20 >> >> This is what the current backup script uses: >> >> /usr/bin/psql -U

Re: [GENERAL] Using cp to back up a database?

2017-10-09 Thread Jeff Janes
On Mon, Oct 9, 2017 at 7:15 AM, Ron Johnson wrote: > > Sure I want a consistent database. Why doesn't? > > But log shipping requires you to rsync/var/lib/pgsql/data to the remote > server, and that's consistent, so why wouldn't rsync to a local directory > also be

Re: [GENERAL] Using cp to back up a database?

2017-10-09 Thread Darren Douglas
Ron: Here is an explanation that may help a bit. Your script is executing a PHYSICAL backup. A physical backup is simply a full copy of the cluster (instance) data directory ($PGDATA). A physical backup is your best option when you need to backup the cluster data as well as all configuration for

Re: [GENERAL] Using cp to back up a database?

2017-10-09 Thread Ron Johnson
On 10/09/2017 11:33 AM, Jeff Janes wrote: On Mon, Oct 9, 2017 at 6:40 AM, Ron Johnson > wrote: Hi, v8.4.20 This is what the current backup script uses: /usr/bin/psql -U postgres -c "SELECT

Re: [GENERAL] Using cp to back up a database?

2017-10-09 Thread Jeff Janes
On Mon, Oct 9, 2017 at 6:40 AM, Ron Johnson wrote: > Hi, > > v8.4.20 > > This is what the current backup script uses: > > /usr/bin/psql -U postgres -c "SELECT pg_start_backup(' > Incrementalbackup',true);" > cp -r /var/lib/pgsql/data/* $dumpdir/data/ > /usr/bin/psql -U

Re: [GENERAL] Using cp to back up a database?

2017-10-09 Thread Achilleas Mantzios
On 09/10/2017 17:13, Michael Paquier wrote: On Mon, Oct 9, 2017 at 11:09 PM, Achilleas Mantzios wrote: In all pg conferences I have been, ppl scream : do not use pg_dump for backups :) It depends on what you are trying to achieve, pg_dump can be fine for

Re: [GENERAL] Using cp to back up a database?

2017-10-09 Thread Ron Johnson
rom: *<pgsql-general-ow...@postgresql.org> on behalf of Ron Johnson <ron.l.john...@cox.net> *Date: *Monday, October 9, 2017 at 8:41 AM *To: *"pgsql-general@postgresql.org" <pgsql-general@postgresql.org> *Subject: *[GENERAL] Using cp to back up a database? Hi, v8.4.20

Re: [GENERAL] Using cp to back up a database?

2017-10-09 Thread Michael Paquier
On Mon, Oct 9, 2017 at 11:09 PM, Achilleas Mantzios wrote: > On 09/10/2017 16:51, Larry Rosenman wrote: > > If you want a consistent database (you *REALLY* do), pg_dump is the correct > tool. > > In all pg conferences I have been, ppl scream : do not use pg_dump for

Re: [GENERAL] Using cp to back up a database?

2017-10-09 Thread Achilleas Mantzios
at 8:41 AM *To: *"pgsql-general@postgresql.org" <pgsql-general@postgresql.org> *Subject: *[GENERAL] Using cp to back up a database? Hi, v8.4.20 This is what the current backup script uses: /usr/bin/psql -U postgres -c "SELECT pg_start_backup('Incrementalbackup',true);" cp

Re: [GENERAL] Using cp to back up a database?

2017-10-09 Thread Melvin Davidson
postgresql.org> on behalf of Ron Johnson < > ron.l.john...@cox.net> > *Date: *Monday, October 9, 2017 at 8:41 AM > *To: *"pgsql-general@postgresql.org" <pgsql-general@postgresql.org> > *Subject: *[GENERAL] Using cp to back up a database? > > > > Hi, > &

Re: [GENERAL] Using cp to back up a database?

2017-10-09 Thread Larry Rosenman
sql-general-ow...@postgresql.org> on behalf of Ron Johnson <ron.l.john...@cox.net> Date: Monday, October 9, 2017 at 8:41 AM To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org> Subject: [GENERAL] Using cp to back up a database? Hi, v8.4.20 This is what th

[GENERAL] Using cp to back up a database?

2017-10-09 Thread Ron Johnson
Hi, v8.4.20 This is what the current backup script uses: /usr/bin/psql -U postgres -c "SELECT pg_start_backup('Incrementalbackup',true);" cp -r /var/lib/pgsql/data/* $dumpdir/data/ /usr/bin/psql -U postgres template1 -c "SELECT pg_stop_backup();" Should it use rsync or pg_dump instead?