pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedquestions about point in time recovery of the primary postgres Server
4+ messages / 4 participants
[nested] [flat]
* questions about point in time recovery of the primary postgres Server
@ 2020-07-24 10:06 pham lan <phamlankt@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: pham lan @ 2020-07-24 10:06 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello,
I am pretty new to Postgres and I have been reading about streaming
replication and point in time recovery which is very interesting to me.
What is not clear to me is: let's say I have a cluster of 1 primary and 2
slaves Postgres 9.5 which have archive_mode on. Streaming replication is
working fine. If for instance, i drop by mistake one table, and i need to
do a restore to a point before the time the table was dropped, will I need
to do the recovery only on the primary postgres server? How will the slaves
behave during the recovery process of the primary?
Please let me know.
Thanks and Best Regards,
Lan Pham
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: questions about point in time recovery of the primary postgres Server
@ 2020-07-24 13:30 Stephen Frost <sfrost@snowman.net>
parent: pham lan <phamlankt@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stephen Frost @ 2020-07-24 13:30 UTC (permalink / raw)
To: pham lan <phamlankt@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Greetings,
* pham lan (phamlankt@gmail.com) wrote:
> I am pretty new to Postgres and I have been reading about streaming
> replication and point in time recovery which is very interesting to me.
> What is not clear to me is: let's say I have a cluster of 1 primary and 2
> slaves Postgres 9.5 which have archive_mode on. Streaming replication is
> working fine. If for instance, i drop by mistake one table, and i need to
> do a restore to a point before the time the table was dropped, will I need
> to do the recovery only on the primary postgres server? How will the slaves
> behave during the recovery process of the primary?
The replicas will need to be rebuilt after the restore of the primary.
There's a few different ways to do that, but the fastest that I'm aware
of would be a pgbackrest delta restore of all the systems using an
appropriate backup set (which pgbackrest will figure out for you when
you tell it the time you want to restore to).
Thanks,
Stephen
Attachments:
[application/pgp-signature] signature.asc (818B, ../../20200724133053.GV12375@tamriel.snowman.net/2-signature.asc)
download
^ permalink raw reply [nested|flat] 4+ messages in thread
* migrating data from non-partitioned table to partitioned table in Aurora Postgresql
@ 2024-02-13 15:44 hellen jiang <hellenjiang99@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: hellen jiang @ 2024-02-13 15:44 UTC (permalink / raw)
To: pgsql-admin
Hi All,
We have 1522 non-partition tables in the Aurora postgres cluster, There are
around 180,000,000 rows in each table, which is very big. We want to change
each table into a partitioned table, so each table will be partitioned into
around 200 partitioned tables: eventually we will get around 1522* 200
tables. Now we are trying to migrate from non-partitioned tables to
partitioned tables by INSERT INTO NEW-TABLE SELECT * FROM OLD-TABLE WHERE
XXX. The problem is this performance is very slow, is there any way that we
can speed up the migration process? by whatever method like changing
configuration. We don't care about database downtime at this moment.
Best Regards,
Hellen Jiang
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: migrating data from non-partitioned table to partitioned table in Aurora Postgresql
@ 2024-02-13 16:15 Ron Johnson <ronljohnsonjr@gmail.com>
parent: hellen jiang <hellenjiang99@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Ron Johnson @ 2024-02-13 16:15 UTC (permalink / raw)
To: hellen jiang <hellenjiang99@gmail.com>; +Cc: pgsql-admin
On Tue, Feb 13, 2024 at 10:44 AM hellen jiang <hellenjiang99@gmail.com>
wrote:
> Hi All,
>
> We have 1522 non-partition tables in the Aurora postgres cluster, There
> are around 180,000,000 rows in each table, which is very big. We want to
> change each table into a partitioned table, so each table will be
> partitioned into around 200 partitioned tables: eventually we will get
> around 1522* 200 tables. Now we are trying to migrate from non-partitioned
> tables to partitioned tables by INSERT INTO NEW-TABLE SELECT * FROM
> OLD-TABLE WHERE XXX. The problem is this performance is very slow,
>
1. Are there indices on NEW-TABLE? (That _always_ slows things down.)
2. Have you tried COPY FROM / COPY TO? (It would require a separate
computer, though, and decent network bandwidth. Maybe spin up an EC2 VM
for that purpose.)
> is there any way that we can speed up the migration process? by whatever
> method like changing configuration. We don't care about database downtime
> at this moment.
>
That would depend on how you've currently configured the instance, which
you haven't mentioned (and might not be valid in vanilla Postgresql).
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-02-13 16:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-07-24 10:06 questions about point in time recovery of the primary postgres Server pham lan <phamlankt@gmail.com>
2020-07-24 13:30 ` Stephen Frost <sfrost@snowman.net>
2024-02-13 15:44 migrating data from non-partitioned table to partitioned table in Aurora Postgresql hellen jiang <hellenjiang99@gmail.com>
2024-02-13 16:15 ` Re: migrating data from non-partitioned table to partitioned table in Aurora Postgresql Ron Johnson <ronljohnsonjr@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox