Re: problema para sustituir una master con una replica
Hola Lista Juan Jose muchas gracias!!! si era eso.. me faltaba incluir en parámetro recovery_target_timeline='latest' y ya.. me funciono tanto en pg11 como en la pg9.5 Por que probe en este último, porque la máquina actual es viejita y la idea es promover en una máquina nueva con mejores prestaciones y en esta si realizar la actualización de versión. El vie., 21 de ago. de 2020 a la(s) 06:57, Juan José Santamaría Flecha ( juanjo.santama...@gmail.com) escribió: > > On Fri, Aug 21, 2020 at 12:22 PM Hellmuth Vargas wrote: > >> Buenos dias >> >> actualice mi servidor a la versión PostgreSQL 11.9 para realizar la >> misma prueba, verificando si el tema era de versión, pero no... el mismo >> error.. osea, estoy haciendo algo mal? >> >> 2020-08-21 04:50:42.961 -05 [16227] LOG: restarted WAL streaming at >> 11/100 on timeline 1 >> 2020-08-21 04:50:42.961 -05 [16227] LOG: replication terminated by >> primary server >> 2020-08-21 04:50:42.961 -05 [16227] DETAIL: End of WAL reached on >> timeline 1 at 11/10001B0. >> > > En el fichero de "recovery.conf", ¿tienes fijado el parámetro > "recovery_target_timeline='latest'"? Si no es así vuelve a probar con ese > valor, y si continúa fallando comparte el contenido del "recovery.conf". > > Un saludo, > > Juan José Santamaría Flecha > >> >> > -- Cordialmente, Ing. Hellmuth I. Vargas S. Esp. Telemática y Negocios por Internet Oracle Database 10g Administrator Certified Associate EnterpriseDB Certified PostgreSQL 9.3 Associate
Re: problema para sustituir una master con una replica
On Fri, Aug 21, 2020 at 12:22 PM Hellmuth Vargas wrote: > Buenos dias > > actualice mi servidor a la versión PostgreSQL 11.9 para realizar la misma > prueba, verificando si el tema era de versión, pero no... el mismo error.. > osea, estoy haciendo algo mal? > > 2020-08-21 04:50:42.961 -05 [16227] LOG: restarted WAL streaming at > 11/100 on timeline 1 > 2020-08-21 04:50:42.961 -05 [16227] LOG: replication terminated by > primary server > 2020-08-21 04:50:42.961 -05 [16227] DETAIL: End of WAL reached on > timeline 1 at 11/10001B0. > En el fichero de "recovery.conf", ¿tienes fijado el parámetro "recovery_target_timeline='latest'"? Si no es así vuelve a probar con ese valor, y si continúa fallando comparte el contenido del "recovery.conf". Un saludo, Juan José Santamaría Flecha > >
Re: problema para sustituir una master con una replica
Buenos dias actualice mi servidor a la versión PostgreSQL 11.9 para realizar la misma prueba, verificando si el tema era de versión, pero no... el mismo error.. osea, estoy haciendo algo mal? bash-4.2$/opt/PostgreSQL/11/bin/initdb -E UTF8 --locale='es_CO.utf8' -U postgres /opt/test/master11/ bash-4.2$nano /opt/test/master11/postgresql.conf bash-4.2$/opt/PostgreSQL/11/bin/pg_upgrade --link --old-datadir /opt/test/master/ --new-datadir /opt/test/master11/ --old-bindir /opt/test/PostgreSQL/9.5/bin/ --new-bindir /opt/PostgreSQL/11/bin/ -p 6432 -P -v bash-4.2$ /opt/PostgreSQL/11/bin/pg_ctl -D /opt/test/master11/ start waiting for server to start2020-08-21 04:38:47.412 -05 [16143] LOG: listening on IPv4 address "0.0.0.0", port 6432 2020-08-21 04:38:47.412 -05 [16143] LOG: listening on IPv6 address "::", port 6432 2020-08-21 04:38:47.413 -05 [16143] LOG: listening on Unix socket "/tmp/.s.PGSQL.6432" 2020-08-21 04:38:47.426 -05 [16143] LOG: redirecting log output to logging collector process 2020-08-21 04:38:47.426 -05 [16143] HINT: Future log output will appear in directory "log". done server started bash-4.2$ cd .. bash-4.2$ cd replica1 bash-4.2$ rm * -Rf bash-4.2$ cd .. bash-4.2$ cd replica2/ bash-4.2$ rm * -Rf bash-4.2$ /opt/PostgreSQL/11/bin/pg_basebackup -D /opt/test/replica1 -F p -X stream -c fast -P -v -R -p 6432 -U postgres pg_basebackup: initiating base backup, waiting for checkpoint to complete pg_basebackup: checkpoint completed pg_basebackup: write-ahead log start point: 11/28 on timeline 1 pg_basebackup: starting background WAL receiver pg_basebackup: created temporary replication slot "pg_basebackup_16172" 32834736/32834736 kB (100%), 1/1 tablespace pg_basebackup: write-ahead log end point: 11/130 pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: base backup completed bash-4.2$ /opt/PostgreSQL/11/bin/pg_ctl -D /opt/test/replica1/ start waiting for server to start2020-08-21 04:44:03.206 -05 [16195] LOG: listening on IPv4 address "0.0.0.0", port 7432 2020-08-21 04:44:03.206 -05 [16195] LOG: listening on IPv6 address "::", port 7432 2020-08-21 04:44:03.207 -05 [16195] LOG: listening on Unix socket "/tmp/.s.PGSQL.7432" 2020-08-21 04:44:03.218 -05 [16195] LOG: redirecting log output to logging collector process 2020-08-21 04:44:03.218 -05 [16195] HINT: Future log output will appear in directory "log". done server started bash-4.2$ bash-4.2$ bash-4.2$ /opt/PostgreSQL/11/bin/pg_basebackup -D /opt/test/replica2 -F p -X stream -c fast -P -v -R -p 7432 -U postgres pg_basebackup: initiating base backup, waiting for checkpoint to complete pg_basebackup: checkpoint completed pg_basebackup: write-ahead log start point: 11/28 on timeline 1 pg_basebackup: starting background WAL receiver pg_basebackup: created temporary replication slot "pg_basebackup_16208" 32834738/32834738 kB (100%), 1/1 tablespace pg_basebackup: write-ahead log end point: 11/160 pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: base backup completed bash-4.2$ bash-4.2$ /opt/PostgreSQL/11/bin/pg_ctl -D /opt/test/replica2/ start waiting for server to start2020-08-21 04:46:05.314 -05 [16221] LOG: listening on IPv4 address "0.0.0.0", port 8432 2020-08-21 04:46:05.314 -05 [16221] LOG: listening on IPv6 address "::", port 8432 2020-08-21 04:46:05.315 -05 [16221] LOG: listening on Unix socket "/tmp/.s.PGSQL.8432" 2020-08-21 04:46:05.326 -05 [16221] LOG: redirecting log output to logging collector process 2020-08-21 04:46:05.326 -05 [16221] HINT: Future log output will appear in directory "log". done server started bash-4.2$ bash-4.2$ bash-4.2$ /opt/PostgreSQL/11/bin/pg_ctl -D /opt/test/master11/ stop -m fast waiting for server to shut down done server stopped bash-4.2$ bash-4.2$ bash-4.2$ /opt/PostgreSQL/11/bin/pg_controldata /opt/test/master11/ | grep 'Latest checkpoint location' Latest checkpoint location: 11/1000140 bash-4.2$ bash-4.2$ /opt/PostgreSQL/11/bin/pg_controldata /opt/test/replica1/ | grep 'Latest checkpoint location' Latest checkpoint location: 11/1000140 bash-4.2$ bash-4.2$ /opt/PostgreSQL/11/bin/pg_controldata /opt/test/replica2/ | grep 'Latest checkpoint location' Latest checkpoint location: 11/1000140 bash-4.2$ bash-4.2$ bash-4.2$ /opt/PostgreSQL/11/bin/pg_ctl -D /opt/test/replica1/ promote waiting for server to promote done server promoted bash-4.2$ # log de la replica2 bash-4.2$ tail -f /opt/test/replica2/log/postgresql-Fri.log 2020-08-21 04:44:03.271 -05 [16195] LOG: database system is ready to accept read only connections 2020-08-21 04:44:03.276 -05 [16201] LOG: started streaming WAL from primary at 11/100 on timeline 1 2020-08-21 04:46:05.328 -05 [16223] LOG: database system was interrupted while in recovery at log time 2020-08-21 04:41:12 -05 2020-08-21 04:46:05.376 -05 [16223] LOG: entering standby mode 2020-08-21 04:46:05.379 -05 [16223] LOG: redo
Re: problema para sustituir una master con una replica
BUenas noches Alvaro Mil gracias por la respuesta, actualice la versión de PostgreSQL 9.5 al ultimo release disponible (9.5.23) y repeti el ejercicio con el mismo resultado :-( bash-4.2$ /opt/test/PostgreSQL/9.5/bin/psql -U postgres -p 6432 postgres psql (9.5.23) Type "help" for help. postgres=# checkpoint; CHECKPOINT postgres=# postgres=# \q bash-4.2$ /opt/test/PostgreSQL/9.5/bin/pg_controldata /opt/test/master/ | grep 'Latest checkpoint location' Latest checkpoint location: F/7F001058 bash-4.2$ /opt/test/PostgreSQL/9.5/bin/pg_controldata /opt/test/replica1/ | grep 'Latest checkpoint location' Latest checkpoint location: F/7F000ED0 bash-4.2$ /opt/test/PostgreSQL/9.5/bin/pg_controldata /opt/test/replica2/ | grep 'Latest checkpoint location' Latest checkpoint location: F/7F000ED0 bash-4.2$ #replica1 bash-4.2$ /opt/test/PostgreSQL/9.5/bin/psql -U postgres -p 7432 postgres psql (9.5.23) Type "help" for help. postgres=# checkpoint; CHECKPOINT postgres=# \q bash-4.2$ /opt/test/PostgreSQL/9.5/bin/pg_controldata /opt/test/replica1/ | grep 'Latest checkpoint location' Latest checkpoint location: F/7F001058 #replica2 bash-4.2$ /opt/test/PostgreSQL/9.5/bin/psql -U postgres -p 8432 postgres psql (9.5.23) Type "help" for help. postgres=# checkpoint; CHECKPOINT postgres=# \q bash-4.2$ /opt/test/PostgreSQL/9.5/bin/pg_controldata /opt/test/replica2/ | grep 'Latest checkpoint location' Latest checkpoint location: F/7F001058 bash-4.2$ bash-4.2$ # bajo master bash-4.2$ /opt/test/PostgreSQL/9.5/bin/pg_ctl -D /opt/test/master/ stop -m fast waiting for server to shut down done server stopped bash-4.2$ # promuevo replica1 bash-4.2$ /opt/test/PostgreSQL/9.5/bin/pg_ctl -D /opt/test/replica1/ promote server promoting # log replica1 FATAL: could not connect to the primary server: could not connect to server: Conexión rehusada Is the server running on host "127.0.0.1" and accepting TCP/IP connections on port 6432? LOG: received promote request LOG: redo done at F/7F001100 LOG: selected new timeline ID: 3 LOG: archive recovery complete LOG: MultiXact member wraparound protections are now enabled LOG: database system is ready to accept connections LOG: autovacuum launcher started # log replica2 bash-4.2$ tail -f /opt/test/replica2/pg_log/postgresql-Thu.log LOG: invalid record length at F/7F001170 LOG: restarted WAL streaming at F/7F00 on timeline 1 LOG: replication terminated by primary server DETAIL: End of WAL reached on timeline 1 at F/7F001170. LOG: restarted WAL streaming at F/7F00 on timeline 1 LOG: replication terminated by primary server DETAIL: End of WAL reached on timeline 1 at F/7F001170. LOG: restarted WAL streaming at F/7F00 on timeline 1 LOG: replication terminated by primary server DETAIL: End of WAL reached on timeline 1 at F/7F001170. LOG: restarted WAL streaming at F/7F00 on timeline 1 LOG: replication terminated by primary server DETAIL: End of WAL reached on timeline 1 at F/7F001170. El jue., 20 de ago. de 2020 a la(s) 16:39, Alvaro Herrera ( alvhe...@2ndquadrant.com) escribió: > Hellmuth Vargas escribió: > > Hola lista tengo un PostgreSQL 9.5.9 > > 9.5.23 es lo más reciente. La cantidad de errores que puede haberse > corregido en todo ese tiempo (tres años menos ocho días) no quiero ni > pensarlo. Actualiza todo y prueba de nuevo; si persiste nos cuentas. > > -- > Álvaro Herrerahttps://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > -- Cordialmente, Ing. Hellmuth I. Vargas S. Esp. Telemática y Negocios por Internet Oracle Database 10g Administrator Certified Associate EnterpriseDB Certified PostgreSQL 9.3 Associate
Re: problema para sustituir una master con una replica
Hellmuth Vargas escribió: > Hola lista tengo un PostgreSQL 9.5.9 9.5.23 es lo más reciente. La cantidad de errores que puede haberse corregido en todo ese tiempo (tres años menos ocho días) no quiero ni pensarlo. Actualiza todo y prueba de nuevo; si persiste nos cuentas. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services