1) What versions of pg_dump and pg_restore did you use?
A: pg_dump and pg_restore was done using pg 14 (the same as the destination was running)
2) To be clear the subscription was started after the restore?
2) To be clear the subscription was started after the restore?
A: Yes
3) Where there any error messages issued at any point in below?
3) Where there any error messages issued at any point in below?
A: no errors during the dump and restore.
4) Are the database clusters on the same machine?
4) Are the database clusters on the same machine?
A: No, the origin and destination were different servers at the same VPC.
On Mon, Nov 7, 2022 at 3:49 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 11/7/22 10:36 AM, Willian Colognesi wrote:
> Hi Tom,
>
> `I take it things were okay with the version you used previously?`
> Yes, it was working pretty well in another instance with pg version
> `12.4-1.pgdg18.04+1`, and we had to make a migration of one database
> that was running in this server to another using Logical Replication.
Actually you used dump/restore and logical replication. '
In below:
1) What versions of pg_dump and pg_restore did you use?
2) To be clear the subscription was started after the restore?
3) Where there any error messages issued at any point in below?
4) Are the database clusters on the same machine?
>
> the process was basically this:
> |CREATE| |PUBLICATION my_database_pub ||FOR| |ALL| |TABLES;|
> |postgres@origin:~$ psql "dbname=<my_database> replication=database"
> |
> |
> |my_database=# CREATE_REPLICATION_SLOT <slot_name> LOGICAL pgoutput;|
> pg_dump -j4 -h <host> -p 5432 --no-subscriptions --no-publications -d
> <my_database> --snapshot=<snapshot_generated> -Fd -U <my_user> -f
> </mnt/dump>
> postgres@destination:/mnt/database$ pg_restore -d <my_database> -j 5
> </mnt/dump>
>
> CREATE SUBSCRIPTION <name_sub>
> CONNECTION 'host=<host> dbname=<my_database> user=replica
> password=?? port=5432'
> PUBLICATION <name_pub>
> WITH (slot_name=<slot_name>, create_slot=false, copy_data=false);
> |
>
>
> After this migration we started to have this kind of problem in both
> replica and primary servers.
>
> `This looks pretty messed up. Are you sure the debug symbols you're using`
> What exactly do you mean? I'm not too familiar with this debug toolings,
> the packages I've used were:
>
> postgresql-14/focal-pgdg,now 14.5-2.pgdg20.04+2 arm64 [installed]
> postgresql-14-dbgsym/focal-pgdg,now 14.5-2.pgdg20.04+2 arm64 [installed]
>
> `Even better, can you construct a self-contained test case?`:
> Actually I couldn't reproduce the problem because it's happening just in
> a production database, and it doesn't look to have a pattern in the
> cases when it happens.
>
> Is there anything I could provide you to help the analysis ?
>
>
>
> On Mon, Nov 7, 2022 at 3:08 PM Tom Lane <tgl@xxxxxxxxxxxxx
> <mailto:tgl@xxxxxxxxxxxxx>> wrote:
>
> Willian Colognesi <willian_colognesi@xxxxxxxxxxx
> <mailto:willian_colognesi@xxxxxxxxxxx>> writes:
> > I started to use version `14.5-2.pgdg20.04+2` for a dedicated
> database and
> > I'm facing many segmentation faults during the day when the
> database has
> > more heavy queries.
>
> I take it things were okay with the version you used previously?
> What was that exactly? Has anything else changed?
>
> > I could also get a little information from gdb, I'm not sure if
> it will
> > help:
>
> This looks pretty messed up. Are you sure the debug symbols you're
> using
> match the package?
>
> Even better, can you construct a self-contained test case?
>
> regards, tom lane
>
>
>
> --
> <http://www.trimble.com/>
> *Willian Cezar de O. Colognesi
> *
> Systems Analysis Specialist, Trimble Transportation Brazil
> Avenida Santos Dumont, 271 | Londrina, PR | 86039-090
>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
Willian Cezar de O. Colognesi
Systems Analysis Specialist, Trimble Transportation Brazil
