pg.ddx.io pgsql-bugs@postgresql.org mailing list archivehelp / color / mirror / Atom feed
BUG #18666: Standby cluster does not start after building from Primary cluster. 4+ messages / 4 participants [nested] [flat]
* BUG #18666: Standby cluster does not start after building from Primary cluster. @ 2024-10-21 10:33 PG Bug reporting form <noreply@postgresql.org> 2024-10-21 14:20 ` Re: BUG #18666: Standby cluster does not start after building from Primary cluster. Tom Lane <tgl@sss.pgh.pa.us> 2024-10-22 01:17 ` Re: BUG #18666: Standby cluster does not start after building from Primary cluster. px shi <spxlyy123@gmail.com> 2024-10-22 01:32 ` Re: BUG #18666: Standby cluster does not start after building from Primary cluster. David G. Johnston <david.g.johnston@gmail.com> 0 siblings, 3 replies; 4+ messages in thread From: PG Bug reporting form @ 2024-10-21 10:33 UTC (permalink / raw) To: pgsql-bugs@lists.postgresql.org; +Cc: andrew.lozovsky@gmail.com The following bug has been logged on the website: Bug reference: 18666 Logged by: Андрій Лозовський Email address: andrew.lozovsky@gmail.com PostgreSQL version: 16.4 Operating system: Ubuntu 20.04.5 LTS Description: Standby cluster does not start after building from Primary cluster. The copy2slave16.sh script was used for the building Standby cluster: #!/bin/bash . /home/postgres/pg.sh LD_LIBRARY_PATH=$PGLIB $PGBIN/psql -Upgadmin -d postgres -f /home/postgres/copy2slave16.sql > /home/postgres/copy.log 2> /home/postgres/copy.err copy2slave16.sql: SELECT pg_backup_start(label => 'label', fast => true); \! rsync -a /opt/pgdata-16/ <Standby_cluster_IP>:/opt/pgdata-16/ --exclude postmaster.pid --exclude pg_wal/ --exclude pg_replslot/ > /home/postgres/rsync.log 2> /home/postgres/rsync.err SELECT * FROM pg_backup_stop(wait_for_archive => true); There was also an attempt to building the Standby cluster using pg_basebackup. Problem was a same. The following messages are indicated in the log file: 2024-10-21 10:38:32.672 EEST [2816535] [] [] [] []LOG: starting PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit 2024-10-21 10:38:32.672 EEST [2816535] [] [] [] []LOG: listening on IPv4 address "0.0.0.0", port 5432 2024-10-21 10:38:32.672 EEST [2816535] [] [] [] []LOG: listening on IPv6 address "::", port 5432 2024-10-21 10:38:32.677 EEST [2816535] [] [] [] []LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2024-10-21 10:38:32.685 EEST [2816540] [] [] [] []LOG: database system was interrupted; last known up at 2024-10-21 06:58:37 EEST cp: cannot stat '/opt/archivelog/00000002.history': No such file or directory 2024-10-21 10:38:32.725 EEST [2816540] [] [] [] []LOG: entering standby mode 2024-10-21 10:38:32.725 EEST [2816540] [] [] [] []LOG: invalid checkpoint record 2024-10-21 10:38:32.725 EEST [2816540] [] [] [] []PANIC: could not locate a valid checkpoint record 2024-10-21 10:38:32.851 EEST [2816535] [] [] [] []LOG: startup process (PID 2816540) was terminated by signal 6: Aborted 2024-10-21 10:38:32.851 EEST [2816535] [] [] [] []LOG: aborting startup due to startup process failure 2024-10-21 10:38:32.851 EEST [2816535] [] [] [] []LOG: [pg_stat_monitor] pgsm_shmem_shutdown: Shutdown initiated. 2024-10-21 10:38:32.872 EEST [2816535] [] [] [] []LOG: database system is shut down The pg_hba.conf file contains all the settings for the primary and standby clusters. The standby.signal file is created in the data directory of the standby cluster. # CUSTOMIZED OPTIONS in postgresql.conf on Standby cluster port = 5432 listen_addresses = '*' superuser_reserved_connections = 3 log_timezone = 'Europe/Kiev' timezone = 'Europe/Kiev' logging_collector = on log_directory = '/opt/pglogs' #log_filename = 'p2p_unk-%a.log' #log_filename = 'p2p_unp-%a.log' log_filename = 'p2p_una-%a.log' log_truncate_on_rotation = on log_line_prefix = '%m [%p] [%a] [%u] [%d] [%h]' log_statement = 'none' # none, ddl, mod, all log_connections = on autovacuum = on shared_preload_libraries = 'pg_stat_statements,pg_stat_monitor' synchronous_commit = on max_locks_per_transaction = 256 cluster_name = 'p2p_un' max_connections = 200 shared_buffers = 16GB effective_cache_size = 32GB maintenance_work_mem = 2GB checkpoint_completion_target = 0.9 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 1.2 effective_io_concurrency = 200 work_mem = 16MB huge_pages = try min_wal_size = 2GB max_wal_size = 8GB max_parallel_workers_per_gather = 4 max_parallel_workers = 16 max_parallel_maintenance_workers = 4 log_min_duration_statement = 1s#min_time_query_in_log archive_mode = on wal_level = replica #logical max_replication_slots = 14 #кількість підписок max_wal_senders = 15 #max_replication_slots+1 max_logical_replication_workers = 15 #max_replication_slots + резерв max_worker_processes = 16 #max_logical_replication_workers + 1 wal_keep_size = 32 #archive_command = 'rsync -aq %p postgres@<Standby_cluster_IP>:/opt/archivelog/%f'#AWS #archive_command = 'rsync -aq %p postgres@<Primary_cluster_IP>:/opt/archivelog/%f'#AWS archive_command = 'cp %p /dev/null' hot_standby = on primary_conninfo = 'user=pgadmin host=<Primary_cluster_IP> port=5432 user=pgadmin sslmode=prefer sslcompression=1' # Amazon #primary_conninfo = 'user=pgadmin host=<Standby_cluster_IP> port=5432 user=pgadmin sslmode=prefer sslcompression=1' # Amazon recovery_target_timeline = 'latest' restore_command = 'cp /opt/archivelog/%f %p' archive_cleanup_command = '/usr/pgsql-16/bin/pg_archivecleanup /opt/archivelog %r' #primary_slot_name = 'standby_slot_unk' #primary_slot_name = 'standby_slot_unp' primary_slot_name = 'standby_slot_una' log_lock_waits = 'on' deadlock_timeout = '100ms' # Parameters of the Server claster. OS - Ubuntu 20.04.5 LTS (Focal Fossa) CPU - 16 RAM - 64 Gb DB Size - 6.7T ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18666: Standby cluster does not start after building from Primary cluster. 2024-10-21 10:33 BUG #18666: Standby cluster does not start after building from Primary cluster. PG Bug reporting form <noreply@postgresql.org> @ 2024-10-21 14:20 ` Tom Lane <tgl@sss.pgh.pa.us> 2 siblings, 0 replies; 4+ messages in thread From: Tom Lane @ 2024-10-21 14:20 UTC (permalink / raw) To: andrew.lozovsky@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org PG Bug reporting form <noreply@postgresql.org> writes: > Standby cluster does not start after building from Primary cluster. I think your archiving setup is broken: > archive_command = 'cp %p /dev/null' > ... > restore_command = 'cp /opt/archivelog/%f %p' BTW, "cp" is widely regarded as not being safe enough for production archiving, because it doesn't fsync. But this archive_command isn't even trying. So it's not surprising if there's not enough WAL to recover from. regards, tom lane ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18666: Standby cluster does not start after building from Primary cluster. 2024-10-21 10:33 BUG #18666: Standby cluster does not start after building from Primary cluster. PG Bug reporting form <noreply@postgresql.org> @ 2024-10-22 01:17 ` px shi <spxlyy123@gmail.com> 2 siblings, 0 replies; 4+ messages in thread From: px shi @ 2024-10-22 01:17 UTC (permalink / raw) To: andrew.lozovsky@gmail.com; pgsql-bugs@lists.postgresql.org > > copy2slave16.sql: > SELECT pg_backup_start(label => 'label', fast => true); > \! rsync -a /opt/pgdata-16/ <Standby_cluster_IP>:/opt/pgdata-16/ --exclude > postmaster.pid --exclude pg_wal/ --exclude pg_replslot/ > > /home/postgres/rsync.log 2> /home/postgres/rsync.err > SELECT * FROM pg_backup_stop(wait_for_archive => true); > How is the archive_command parameter configured on your primary? I see primary and standby nodes aren't deployed on the same host, so is it set up to send WAL files to the /opt/archivelog directory on the standby machine? Regards, Pixian Shi PG Bug reporting form <noreply@postgresql.org> 于2024年10月21日周一 19:33写道： > The following bug has been logged on the website: > > Bug reference: 18666 > Logged by: Андрій Лозовський > Email address: andrew.lozovsky@gmail.com > PostgreSQL version: 16.4 > Operating system: Ubuntu 20.04.5 LTS > Description: > > Standby cluster does not start after building from Primary cluster. > > The copy2slave16.sh script was used for the building Standby cluster: > #!/bin/bash > . /home/postgres/pg.sh > LD_LIBRARY_PATH=$PGLIB $PGBIN/psql -Upgadmin -d postgres -f > /home/postgres/copy2slave16.sql > /home/postgres/copy.log 2> > /home/postgres/copy.err > > copy2slave16.sql: > SELECT pg_backup_start(label => 'label', fast => true); > \! rsync -a /opt/pgdata-16/ <Standby_cluster_IP>:/opt/pgdata-16/ --exclude > postmaster.pid --exclude pg_wal/ --exclude pg_replslot/ > > /home/postgres/rsync.log 2> /home/postgres/rsync.err > SELECT * FROM pg_backup_stop(wait_for_archive => true); > > There was also an attempt to building the Standby cluster using > pg_basebackup. > Problem was a same. > > The following messages are indicated in the log file: > > 2024-10-21 10:38:32.672 EEST [2816535] [] [] [] []LOG: starting PostgreSQL > 16.4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu > 9.4.0-1ubuntu1~20.04.1) > 9.4.0, 64-bit > 2024-10-21 10:38:32.672 EEST [2816535] [] [] [] []LOG: listening on IPv4 > address "0.0.0.0", port 5432 > 2024-10-21 10:38:32.672 EEST [2816535] [] [] [] []LOG: listening on IPv6 > address "::", port 5432 > 2024-10-21 10:38:32.677 EEST [2816535] [] [] [] []LOG: listening on Unix > socket "/tmp/.s.PGSQL.5432" > 2024-10-21 10:38:32.685 EEST [2816540] [] [] [] []LOG: database system was > interrupted; last known up at 2024-10-21 06:58:37 EEST > cp: cannot stat '/opt/archivelog/00000002.history': No such file or > directory > 2024-10-21 10:38:32.725 EEST [2816540] [] [] [] []LOG: entering standby > mode > 2024-10-21 10:38:32.725 EEST [2816540] [] [] [] []LOG: invalid checkpoint > record > 2024-10-21 10:38:32.725 EEST [2816540] [] [] [] []PANIC: could not locate > a > valid checkpoint record > 2024-10-21 10:38:32.851 EEST [2816535] [] [] [] []LOG: startup process > (PID > 2816540) was terminated by signal 6: Aborted > 2024-10-21 10:38:32.851 EEST [2816535] [] [] [] []LOG: aborting startup > due > to startup process failure > 2024-10-21 10:38:32.851 EEST [2816535] [] [] [] []LOG: [pg_stat_monitor] > pgsm_shmem_shutdown: Shutdown initiated. > 2024-10-21 10:38:32.872 EEST [2816535] [] [] [] []LOG: database system is > shut down > > The pg_hba.conf file contains all the settings for the primary and standby > clusters. > The standby.signal file is created in the data directory of the standby > cluster. > > # CUSTOMIZED OPTIONS in postgresql.conf on Standby cluster > port = 5432 > listen_addresses = '*' > superuser_reserved_connections = 3 > log_timezone = 'Europe/Kiev' > timezone = 'Europe/Kiev' > logging_collector = on > log_directory = '/opt/pglogs' > #log_filename = 'p2p_unk-%a.log' > #log_filename = 'p2p_unp-%a.log' > log_filename = 'p2p_una-%a.log' > log_truncate_on_rotation = on > log_line_prefix = '%m [%p] [%a] [%u] [%d] [%h]' > log_statement = 'none' # none, ddl, mod, all > log_connections = on > autovacuum = on > shared_preload_libraries = 'pg_stat_statements,pg_stat_monitor' > synchronous_commit = on > max_locks_per_transaction = 256 > cluster_name = 'p2p_un' > > max_connections = 200 > shared_buffers = 16GB > effective_cache_size = 32GB > maintenance_work_mem = 2GB > checkpoint_completion_target = 0.9 > wal_buffers = 16MB > default_statistics_target = 100 > random_page_cost = 1.2 > effective_io_concurrency = 200 > work_mem = 16MB > huge_pages = try > min_wal_size = 2GB > max_wal_size = 8GB > max_parallel_workers_per_gather = 4 > max_parallel_workers = 16 > max_parallel_maintenance_workers = 4 > log_min_duration_statement = 1s#min_time_query_in_log > > archive_mode = on > wal_level = replica #logical > max_replication_slots = 14 #кількість підписок > max_wal_senders = 15 #max_replication_slots+1 > max_logical_replication_workers = 15 #max_replication_slots + резерв > max_worker_processes = 16 #max_logical_replication_workers + > 1 > wal_keep_size = 32 > > #archive_command = 'rsync -aq %p > postgres@<Standby_cluster_IP>:/opt/archivelog/%f'#AWS > #archive_command = 'rsync -aq %p > postgres@<Primary_cluster_IP>:/opt/archivelog/%f'#AWS > archive_command = 'cp %p /dev/null' > > hot_standby = on > > primary_conninfo = 'user=pgadmin host=<Primary_cluster_IP> port=5432 > user=pgadmin sslmode=prefer sslcompression=1' # Amazon > #primary_conninfo = 'user=pgadmin host=<Standby_cluster_IP> port=5432 > user=pgadmin sslmode=prefer sslcompression=1' # Amazon > recovery_target_timeline = 'latest' > restore_command = 'cp /opt/archivelog/%f %p' > archive_cleanup_command = '/usr/pgsql-16/bin/pg_archivecleanup > /opt/archivelog %r' > #primary_slot_name = 'standby_slot_unk' > #primary_slot_name = 'standby_slot_unp' > primary_slot_name = 'standby_slot_una' > > log_lock_waits = 'on' > deadlock_timeout = '100ms' > > # Parameters of the Server claster. > OS - Ubuntu 20.04.5 LTS (Focal Fossa) > CPU - 16 > RAM - 64 Gb > DB Size - 6.7T > > ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18666: Standby cluster does not start after building from Primary cluster. 2024-10-21 10:33 BUG #18666: Standby cluster does not start after building from Primary cluster. PG Bug reporting form <noreply@postgresql.org> @ 2024-10-22 01:32 ` David G. Johnston <david.g.johnston@gmail.com> 2 siblings, 0 replies; 4+ messages in thread From: David G. Johnston @ 2024-10-22 01:32 UTC (permalink / raw) To: andrew.lozovsky@gmail.com <andrew.lozovsky@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org> On Monday, October 21, 2024, PG Bug reporting form <noreply@postgresql.org> wrote: > The following bug has been logged on the website: > > Bug reference: 18666 > Logged by: Андрій Лозовський > Email address: andrew.lozovsky@gmail.com > PostgreSQL version: 16.4 > Operating system: Ubuntu 20.04.5 LTS > Description: > > There isn’t likely to be a bug here. > > There was also an attempt to building the Standby cluster using > pg_basebackup. > Problem was a same. I suggest to request help on using pg_basebackup on the -general mailing list. David J. ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-10-22 01:32 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-10-21 10:33 BUG #18666: Standby cluster does not start after building from Primary cluster. PG Bug reporting form <noreply@postgresql.org> 2024-10-21 14:20 ` Tom Lane <tgl@sss.pgh.pa.us> 2024-10-22 01:17 ` px shi <spxlyy123@gmail.com> 2024-10-22 01:32 ` David G. Johnston <david.g.johnston@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