Hi Stefan,
stupid me - Ive missed some
with RECURSIVE qq(cont_key, anc_key) AS
(
SELECT
a1.context_key, ancestor_key
FROM
virtual_ancestors a1
UNION select ( -- here, in the union
SELECT
a1.context_key, a1.ancestor_key
FROM
virtual_ancestors
Hi gianfranco,
How exactly large is your database and how heavy is a workload on it?
Usually if you have more than ~200Gb, better to use pg_basebackup
because pg_dump will take too long time. And please take in mind, that
pg_dump makes dump, which is actually not the same thing as a backup.
Hai ilya,
Thanks for the respond. The database is estimated over 100gb and the workload
will be high. Can we use a pg_basebackup with pitr to restore based on
transaction time?
Thanks
On Tuesday, 25 March 2014, 15:13, Ilya Kosmodemiansky
ilya.kosmodemian...@postgresql-consulting.com
Yes, you need to set recovery_target_time in your recovery.conf while
performing recovery
(http://www.postgresql.org/docs/9.3/static/recovery-target-settings.html).
That could be a tricky thing - depends on that exactly you need. All
those transactions, which were not committed at given timestamp,
gianfranco caca wrote
Hai,
Can anyone tell me the difference and performance between pgdump and
pg_basebackup if I want to backup a large database.
Thanks
Yes. And many of their words have been written down in the documentation in
a chapter named Backup and Restore. Do you have a
On Tue, Mar 25, 2014 at 4:39 AM, David Johnston pol...@yahoo.com wrote:
Hai,
Can anyone tell me the difference and performance between pgdump and
pg_basebackup if I want to backup a large database.
Thanks
Yes. And many of their words have been written down in the documentation in
a
Hi everyone!
I've been working on a puzzling issue for a few days am am hoping that someone
has seen something similar or can help. There have been some odd behaviors on
one of my production facing postgres servers.
version info from postgres: PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu,
2014-03-25 15:56 GMT+01:00 Joshua D. Drake j...@commandprompt.com:
On 03/25/2014 05:05 AM, Claudio Freire wrote:
On Tue, Mar 25, 2014 at 4:39 AM, David Johnston pol...@yahoo.com wrote:
Hai,
Can anyone tell me the difference and performance between pgdump and
pg_basebackup if I want to
Joshua,
that is really good point: an alternative is to use pg_basebackup
through ssh tunnel with compression, but rsync is much simpler.
On Tue, Mar 25, 2014 at 3:56 PM, Joshua D. Drake j...@commandprompt.com wrote:
On 03/25/2014 05:05 AM, Claudio Freire wrote:
On Tue, Mar 25, 2014 at 4:39
On 03/25/2014 05:05 AM, Claudio Freire wrote:
On Tue, Mar 25, 2014 at 4:39 AM, David Johnston pol...@yahoo.com wrote:
Hai,
Can anyone tell me the difference and performance between pgdump and
pg_basebackup if I want to backup a large database.
Honestly,
Neither is particularly good at
On 03/25/2014 08:18 AM, Ilya Kosmodemiansky wrote:
Joshua,
that is really good point: an alternative is to use pg_basebackup
through ssh tunnel with compression, but rsync is much simpler.
Or rsync over ssh. The advantage is that you can create backups that
don't have to be restored, just
Joshua,
On Tue, Mar 25, 2014 at 4:22 PM, Joshua D. Drake j...@commandprompt.com wrote:
The advantage is that you can create backups that don't
have to be restored, just started. You can also use the differential
portions of rsync to do it multiple times a day without much issue.
Are you sure,
Magnus,
That is correct, but I'am afraid that such all-in-one functionality
also hides from one how backup really works. Probably such sort of
knowledge is so essential for a DBA, that it is better to learn both
methods, at least to be able to choose correctly? But maybe it is a
rhetorical
Oh, I agree it's good that you should know both methods. I only disagree
with that the choice of rsync be made with the argument of simplicity.
Simplicity is one of the main reasons to choose the *other* method
(pg_basebackup), and the rsync method is for more advanced usecases. But
it's
OK, agreed. Ive got your point;-)
On Tue, Mar 25, 2014 at 4:40 PM, Magnus Hagander mag...@hagander.net wrote:
Oh, I agree it's good that you should know both methods. I only disagree
with that the choice of rsync be made with the argument of simplicity.
Simplicity is one of the main reasons to
Postgresql rsync backups require the DB to be shutdown during the 'second'
rsync.
1. rsync the DB onto the backup filesystem (produces e.g. 95-99.99% consistent
DB on the backup filesystem)
2. shut down the DB
3. rsync the shut down DB onto the backup filesystem(synchronises the last
few
On 03/25/2014 08:21 AM, Magnus Hagander wrote:
I would say that's the one thing that rsync is *not*. pg_basebackup
takes care of a lot of things under the hood. rsync is a lot more
complicated, in particular in failure scenarios, since you have to
manually deal with pg_start/stop_backup().
On Tuesday, March 25, 2014 03:48:07 PM Graeme B. Bell wrote:
Postgresql rsync backups require the DB to be shutdown during the 'second'
rsync.
1. rsync the DB onto the backup filesystem (produces e.g. 95-99.99%
consistent DB on the backup filesystem) 2. shut down the DB
3. rsync the shut
On Tue, Mar 25, 2014 at 12:22 PM, Joshua D. Drake j...@commandprompt.com
wrote:
On 03/25/2014 08:18 AM, Ilya Kosmodemiansky wrote:
Joshua,
that is really good point: an alternative is to use pg_basebackup
through ssh tunnel with compression, but rsync is much simpler.
Or rsync over ssh.
On Mar 25, 2014, at 8:46 AM, Matthew Spilich wrote:
The symptom: The database machine (running postgres 9.1.9 on CentOS 6.4) is
running a low utilization most of the time, but once every day or two, it
will appear to slow down to the point where queries back up and clients are
unable to
Thanks all:
Ray: Thanks, we started to look at the hardware/firmware, but didn't get to
the the level of detail or running sar. I will probably collect more detail
in this area if I continue to see issues.
Pavy - I hope that you are right that the hugepage setting is the issue. I
was
2014-03-25, Matthew Spilich mspil...@tripadvisor.com:
Has any on the forum seen something similar? Any suggestions on what
to look at next?If it is helpful to describe the server hardware, it's
got 2 E5-2670 cpu and 256 GB of ram, and the database is hosted on 1.6TB raid
10 local
22 matches
Mail list logo
