Thread: Logical replication troubleshooting
Hello Team!
We are trying to migrate a on-premise PgSQL (v10) to GCP Cloud SQL's PgSQL (v10) via PubSub replication. I am trying to get familiar with best practices in this context. We built 2 publications - one for all tables with Primary Keys and other for non-Primary Keys. There are few archive tables with non-primary key are having issues in the replication. How do we troubleshoot this? Is it a best practice to bifurcate publications this way?
Regards
Siraj
Hi Siraj
It all depends on your requirements, you can use as many publication as you want as per your requirements.
Regards
Kashif Zeeshan
On Mon, Jun 24, 2024 at 7:50 AM Siraj G <tosiraj.g@gmail.com> wrote:
Hello Team!We are trying to migrate a on-premise PgSQL (v10) to GCP Cloud SQL's PgSQL (v10) via PubSub replication. I am trying to get familiar with best practices in this context. We built 2 publications - one for all tables with Primary Keys and other for non-Primary Keys. There are few archive tables with non-primary key are having issues in the replication. How do we troubleshoot this? Is it a best practice to bifurcate publications this way?RegardsSiraj
Hi Siraj,
Please go through PostgreSQL documentation first to develop better understanding.
Later you may go through some videos on Youtube or google. There are tons of step by step examples available.
Regards,
Muhammad Ikram
Bitnine Global
On Mon, Jun 24, 2024 at 7:50 AM Siraj G <tosiraj.g@gmail.com> wrote:
Hello Team!We are trying to migrate a on-premise PgSQL (v10) to GCP Cloud SQL's PgSQL (v10) via PubSub replication. I am trying to get familiar with best practices in this context. We built 2 publications - one for all tables with Primary Keys and other for non-Primary Keys. There are few archive tables with non-primary key are having issues in the replication. How do we troubleshoot this? Is it a best practice to bifurcate publications this way?RegardsSiraj
Muhammad Ikram
On Mon, 2024-06-24 at 08:20 +0530, Siraj G wrote: > We are trying to migrate a on-premise PgSQL (v10) to GCP Cloud SQL's PgSQL (v10) via PubSub replication. > There are few archive tables with non-primary key are having issues in the replication. That's not surprising. You should create primary keys, if necessary only for the replication. Yours, Laurenz Albe
Hello Admins!
I am getting this error again & again when trying to create a replication for 22M records table.
INFO 2024-06-24T07:21:39.348016Z 2024-06-24 12:51:39.347 IST [400674]: [1-1] db=,user= LOG: logical replication table synchronization worker for subscription "sub_room_inventory", table "room_inventory" has started
ERROR 2024-06-24T09:01:11.559646Z 2024-06-24 14:31:11.559 IST [400674]: [2-1] db=,user= ERROR: could not receive data from WAL stream: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.
INFO 2024-06-24T09:01:11.561358Z 2024-06-24 14:31:11.561 IST [7]: [106-1] db=,user= LOG: worker process: logical replication worker for subscription 95898332 sync 69720 (PID 400674) exited with exit code 1
ERROR 2024-06-24T09:01:11.559646Z 2024-06-24 14:31:11.559 IST [400674]: [2-1] db=,user= ERROR: could not receive data from WAL stream: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.
INFO 2024-06-24T09:01:11.561358Z 2024-06-24 14:31:11.561 IST [7]: [106-1] db=,user= LOG: worker process: logical replication worker for subscription 95898332 sync 69720 (PID 400674) exited with exit code 1
On the target, i have these values set:
wal_sender_timeout=15min
wal_receiver_timeout=1hr
Please advice.
Regards
Siraj
On Mon, Jun 24, 2024 at 1:40 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Mon, 2024-06-24 at 08:20 +0530, Siraj G wrote:
> We are trying to migrate a on-premise PgSQL (v10) to GCP Cloud SQL's PgSQL (v10) via PubSub replication.
> There are few archive tables with non-primary key are having issues in the replication.
That's not surprising. You should create primary keys, if necessary only for the replication.
Yours,
Laurenz Albe