logical replication problem
Hi!
Setup:
- db-server_A on port 5432
- db-server_B on port 5433
on db-server_A:
postgres=# \dRp
Liste der Publikationen
Name | Eigentümer | Alle Tabellen | Inserts | Updates | Deletes
--++---+-+-+-
mig1 | postgres | t | t | t | t
on db-server_B:
postgres=# \dRs
Liste der Subskriptionen
Name | Eigentümer | Eingeschaltet | Publikation
--++---+-
sub1 | postgres | t | {mig1}
in db-server_A pg_hba.conf:
local replication postgrespeer
hostreplication postgres127.0.0.1/32ident
hostreplication postgres::1/128 ident
on db-server_A:
postgres# CREATE PUBLICATION mig1 FOR ALL TABLES;
CREATE PUBLICATION
the command on db-server_B:
postgres# CREATE SUBSCRIPTION sub1 CONNECTION 'host=127.0.0.1
port=5432 dbname=mydb user=postgres PUBLICATION mig1;
NOTICE: created replication slot "sub1" on publisher
CREATE SUBSCRIPTION
worked as expected.
But: instead of starting replication I find Errors within db-server_B logs:
2019-01-24 10:57:58.549 CET [28956] LOG: Apply-Worker für logische
Replikation für Subskription »sub1« hat gestartet
2019-01-24 10:57:58.553 CET [28956] FEHLER: konnte keine Daten vom
WAL-Stream empfangen: FEHLER: Publikation »mig1« existiert nicht
KONTEXT: Slot »sub1«, Ausgabe-Plugin »pgoutput«, im Callback
change, zugehörige LSN 47/B4BCA2A8
2019-01-24 10:57:58.554 CET [5982] LOG: Background-Worker »logical
replication worker« (PID 28956) beendete mit Status 1
Configuration was set on both servers to include
wal_level = logical
Any ideas why this does not work as expected? Any further ideas what
to prove on db-server_A and db-server_B?
--
Thomas
Re: logical replication problem
On Mon, Jan 28, 2019 at 11:15 AM Pavan Teja wrote:
>
> Does the db_server_B has data in it??
Yes, it has -- about 51GiByte ... Changed frequently.
> Regards,
> Pavan
>
> On Mon 28 Jan, 2019, 3:42 PM Thomas Schweikle >
>> Hi!
>>
>> Setup:
>>
>> - db-server_A on port 5432
>> - db-server_B on port 5433
>>
>> on db-server_A:
>> postgres=# \dRp
>> Liste der Publikationen
>> Name | Eigentümer | Alle Tabellen | Inserts | Updates | Deletes
>> --++---+-+-+-
>> mig1 | postgres | t | t | t | t
>>
>> on db-server_B:
>> postgres=# \dRs
>> Liste der Subskriptionen
>> Name | Eigentümer | Eingeschaltet | Publikation
>> --++---+-
>> sub1 | postgres | t | {mig1}
>>
>> in db-server_A pg_hba.conf:
>> local replication postgrespeer
>> hostreplication postgres127.0.0.1/32ident
>> hostreplication postgres::1/128 ident
>>
>> on db-server_A:
>> postgres# CREATE PUBLICATION mig1 FOR ALL TABLES;
>> CREATE PUBLICATION
>>
>> the command on db-server_B:
>> postgres# CREATE SUBSCRIPTION sub1 CONNECTION 'host=127.0.0.1
>> port=5432 dbname=mydb user=postgres PUBLICATION mig1;
>> NOTICE: created replication slot "sub1" on publisher
>> CREATE SUBSCRIPTION
>>
>> worked as expected.
>> But: instead of starting replication I find Errors within db-server_B logs:
>> 2019-01-24 10:57:58.549 CET [28956] LOG: Apply-Worker für logische
>> Replikation für Subskription »sub1« hat gestartet
>> 2019-01-24 10:57:58.553 CET [28956] FEHLER: konnte keine Daten vom
>> WAL-Stream empfangen: FEHLER: Publikation »mig1« existiert nicht
>> KONTEXT: Slot »sub1«, Ausgabe-Plugin »pgoutput«, im Callback
>> change, zugehörige LSN 47/B4BCA2A8
>> 2019-01-24 10:57:58.554 CET [5982] LOG: Background-Worker »logical
>> replication worker« (PID 28956) beendete mit Status 1
>>
>> Configuration was set on both servers to include
>> wal_level = logical
>>
>> Any ideas why this does not work as expected? Any further ideas what
>> to prove on db-server_A and db-server_B?
>>
>>
>> --
>> Thomas
>>
--
Thomas
Re: Postgres service not starting on windows after install if not installed into standard locations
Am Mo., 18.Nov..2024 um 18:48:56 schrieb Adrian Klaver: > On 11/18/24 09:22, Thomas Schweikle wrote: >> Hi! >> >> Had installed PostgreSQL 16.5 on Windows 10: > > Using what installer? postgresql-16.5-1-windows-x64.exe as found on https://www.enterprisedb.com/downloads/postgres-postgresql-downloads >> Install directory: "C:\PRGS\pgSQL\16" >> DB-Directory: "C:\DB\pgSQL\16\data" >> >> Environment variable "PGDATA" is set to "C:\DB\pgSQL\16\data". >> >> Initialized the database. Then started postgres services using: >> >> c:\PRGS\pgSQL\16\bin\pg_ctl.exe start >> >> Worked nice. Server starts, the serves as expected. But: if I try to >> start the service by "net start pgSQL" I am reported that "pg_ctl.exe" >> could not find "postgres.exe". >> >> What I've tried: >> - added environment variable "PGPATH" pointing to "C:\PRGS\pgSQL\16\bin" >> -> did not help. >> >> - added "C:\PRGS\pgSQL\16\bin" to path. -> did not help either, even >> after rebooting. I've added it to system path. >> >> Are paths somewhere within some configuration file or registry entry? >> And set to standard install locations? >> >> Any idea is welcome to solve this problem! -- Thomas
Re: Postgres service not starting on windows after install if not installed into standard locations
Am Mo., 18.Nov..2024 um 19:43:36 schrieb Laurenz Albe: > On Mon, 2024-11-18 at 17:22 +0000, Thomas Schweikle wrote: >> Had installed PostgreSQL 16.5 on Windows 10: >> >> Install directory: "C:\PRGS\pgSQL\16" >> DB-Directory: "C:\DB\pgSQL\16\data" >> >> Environment variable "PGDATA" is set to "C:\DB\pgSQL\16\data". >> >> Initialized the database. Then started postgres services using: >> >> c:\PRGS\pgSQL\16\bin\pg_ctl.exe start >> >> Worked nice. Server starts, the serves as expected. But: if I try to >> start the service by "net start pgSQL" I am reported that "pg_ctl.exe" >> could not find "postgres.exe". >> >> What I've tried: >> - added environment variable "PGPATH" pointing to "C:\PRGS\pgSQL\16\bin" >> -> did not help. >> >> - added "C:\PRGS\pgSQL\16\bin" to path. -> did not help either, even >> after rebooting. I've added it to system path. >> >> Are paths somewhere within some configuration file or registry entry? >> And set to standard install locations? > > How exactly is the Windows service defined? > Perhaps you need to use an absolute path for the executable. Servicename: postgresql-x64-16 Name:postgresql-x64-16-PostgreSQL Server 16 Comment: Provides relational database storage Path:"C:\PRGS\pgSQL\16\bin\pg_ctl.exe" runservice -N "postgresql-x64-16" -D "C:\DB\pgSQL\16\data" -w Type:Automatic Account: Networkservice Password:set Dependencies:RPC, DCOM-Service, RPC-Endpoint Errors found within system logs: pg_ctl: could not find program 'postgres.exe' Seems, since "C:\PRGS\pgSQL\16\bin" is in system path, it does not search there at all: # dir "C:\PRGS\pgSQL\16\bin" [...] Mi, 13.Nov.2024 13:24 123.392 pg_ctl.exe [...] Mi, 13.Nov.2024 13:24 8.659.968 postgres.exe Any way to tell a service in Windows to use a path? Any registry key to assign a path to? # sc qc "postgresql-x64-16" [SC] QueryServiceConfig ERFOLG SERVICE_NAME: postgresql-x64-16 TYPE : 10 WIN32_OWN_PROCESS START_TYPE : 2 AUTO_START ERROR_CONTROL : 1 NORMAL BINARY_PATH_NAME : "C:\PRGS\pgSQL\16\bin\pg_ctl.exe" runservice -N "postgresql-x64-16" -D "C:\DB\pgSQL\16\data" -w LOAD_ORDER_GROUP : TAG: 0 DISPLAY_NAME : postgresql-x64-16 - PostgreSQL Server 16 DEPENDENCIES : RPCSS SERVICE_START_NAME : NT AUTHORITY\NetworkService -- Thomas
Postgres service not starting on windows after install if not installed into standard locations
Hi! Had installed PostgreSQL 16.5 on Windows 10: Install directory: "C:\PRGS\pgSQL\16" DB-Directory: "C:\DB\pgSQL\16\data" Environment variable "PGDATA" is set to "C:\DB\pgSQL\16\data". Initialized the database. Then started postgres services using: c:\PRGS\pgSQL\16\bin\pg_ctl.exe start Worked nice. Server starts, the serves as expected. But: if I try to start the service by "net start pgSQL" I am reported that "pg_ctl.exe" could not find "postgres.exe". What I've tried: - added environment variable "PGPATH" pointing to "C:\PRGS\pgSQL\16\bin" -> did not help. - added "C:\PRGS\pgSQL\16\bin" to path. -> did not help either, even after rebooting. I've added it to system path. Are paths somewhere within some configuration file or registry entry? And set to standard install locations? Any idea is welcome to solve this problem! -- Thomas
Re: Postgres service not starting on windows after install if not installed into standard locations
Am Mi., 20.Nov..2024 um 18:15:42 schrieb Adrian Klaver: > On 11/20/24 05:26, Thomas Schweikle wrote: >> Am Mo., 18.Nov..2024 um 19:43:36 schrieb Laurenz Albe: >>> On Mon, 2024-11-18 at 17:22 +, Thomas Schweikle wrote: >>>> Had installed PostgreSQL 16.5 on Windows 10: > >>> How exactly is the Windows service defined? >>> Perhaps you need to use an absolute path for the executable. >> >> Servicename: postgresql-x64-16 >> Name: postgresql-x64-16-PostgreSQL Server 16 >> Comment: Provides relational database storage >> Path: "C:\PRGS\pgSQL\16\bin\pg_ctl.exe" >> runservice -N "postgresql-x64-16" >> -D "C:\DB\pgSQL\16\data" -w >> Type: Automatic >> Account: Networkservice >> Password: set >> Dependencies:RPC, DCOM-Service, RPC-Endpoint > > > Did you change settings during the install? I called the installer with: .\postgresql-16.5-1-windows-x64.exe Then changed install path to: "C:\PRGS\pgSQL" And database path to: "C:\DB\pgSQL" After a while postgres got installed into "C:\PRGS\pgSQL\16" and the database path was created: "C:\DB\pgSQL\16\data", but the database was not created. Calling pg_initdb did the job and created the database within the given folder. Calling "pg_ctl start" gave a message, that "pg_ctl.exe" could not find "postgres.exe" -- I prepended "C:\PRGS\pgSQL\16\bin" to %PATH%. Next thing was that pg_ctl could not find the just, with pg_initdb, created database. So I've set up PGDATA to point to "C:\DB\pgSQL\16\data". Both within the global system environment of windows. Tried again to start postgres via "pg_ctl start". This time postgres got up and running instantly. Then stopped postgres again: "pg_ctl.exe stop". And tried it using "net start postgresql-x64-16". This failed. "pg_ctl.exe" called could not find "postgres.exe". Looking at the logs "pg_ctl.exe" stated it could not find "postgres.exe" again. Since %PGDATA% and %PATH% where set within system environment these are to be global and shall be active for services too. Looking closer on "pg_ctl.exe" I found it using a build in path of "C:\Program files\PostgreSQL\16\bin\postgres.exe", regardless of any environment variables set if started without any of stdout, stdin, stderr, what is the case if it is started as a service. > Here on my Windows machine I get: > > C:\Program Files\PostgreSQL\bin >> Errors found within system logs: >> pg_ctl: could not find program 'postgres.exe' >> >> Seems, since "C:\PRGS\pgSQL\16\bin" is in system path, it does not >> search there at all: >> >> # dir "C:\PRGS\pgSQL\16\bin" >> [...] >> Mi, 13.Nov.2024 13:24 123.392 pg_ctl.exe >> [...] >> Mi, 13.Nov.2024 13:24 8.659.968 postgres.exe >> >> Any way to tell a service in Windows to use a path? >> Any registry key to assign a path to? >> >> # sc qc "postgresql-x64-16" >> [SC] QueryServiceConfig ERFOLG >> >> SERVICE_NAME: postgresql-x64-16 >> TYPE : 10 WIN32_OWN_PROCESS >> START_TYPE : 2 AUTO_START >> ERROR_CONTROL : 1 NORMAL >> BINARY_PATH_NAME : "C:\PRGS\pgSQL\16\bin\pg_ctl.exe" >> runservice -N "postgresql-x64-16" -D "C:\DB\pgSQL\16\data" -w >> LOAD_ORDER_GROUP : >> TAG : 0 >> DISPLAY_NAME : postgresql-x64-16 - PostgreSQL Server 16 >> DEPENDENCIES : RPCSS >> SERVICE_START_NAME : NT AUTHORITY\NetworkService >> >> > -- Thomas
