Re: psql and pgpass.conf on Windows
On Fri, Jun 30, 2023 at 8:39 AM wrote: > On Fri, 30 Jun 2023 11:16:36 +0800 Julien Rouhaud wrote: > > >Hi, > > > >On Thu, Jun 29, 2023 at 10:42:00PM -0400, p...@pfortin.com wrote: > >> > >> Windows: %APPDATA%\postgresql\pgpass.conf > >> > > echo %APPDATA% > C:\Users\Pierre\AppData\Roaming > > >Now, since setting PGPASSFILE also doesn't work I start to wonder if > there's > >another problem. Does the password (or any other field) contain some > non-ASCII > >characters? > > type %APPDATA%\postgresql\pgpass.conf > 127.0.0.1:5432:ncsbe:postgres: > (only ASCII characters and no trailing space) > was using "localhost" before trying 127.0.0.1 > even "*:*:..." failed. > > Pierre, I use my pgpass .conf in windows. copy that file to your current directory. and set PGPASSFILE=pgpass.conf and try to get in. Next, please specify the complete command line for psql you are using... This way you know you are not passing in a strange variable. If I set PGPASSFILE to a bad filename, I get a password prompt. But the only time I've seen this was someone not specifying the dbname correctly. FWIW, I discovered that psql is case sensitive on the dbname, without quoting it! HTH, Kirk
Is anyone using db_user_namespace?
Over in pgsql-hackers, I've proposed removing the db_user_namespace parameter in v17 [0]. I am personally not aware of anyone using this parameter, but I wanted to give folks an opportunity to object in case they are using it (or are aware of someone who is). [0] https://postgr.es/m/20230630200509.GA2830328%40nathanxps13 -- Nathan Bossart Amazon Web Services: https://aws.amazon.com
Re: Toasted column values during replication
On Fri, 2023-06-30 at 17:06 +, Chandy G wrote: > For instance : Can the postgres internal component (wal-sender / *) > interpret this special > toasted-col-value sitting in the wal-file -> replace it with the actual value > and send it to > the replication slot. Which logical decoding plugin are you using? "pgoutput", which is provided by PostgreSQL, will surely emit properly detoasted values. Yours, Laurenz Albe
Toasted column values during replication
Hi PG Users, We are running into the postgres toasted values showing up in replication slots. We are using debezium and have gone through the following 1. Various options for handling toasted values during replication at the application level.2. Postgres option to turn on replica identitiy to full. While these are solutions to the problem. Wanted to understand if there is any scope for enhancement / improvements in postgres itself to handle such scenarios. For instance : Can the postgres internal component (wal-sender / *) interpret this special toasted-col-value sitting in the wal-file -> replace it with the actual value and send it to the replication slot. I presume, today the wal-sender does not have this support and it just sends whatever data that the wal log has. So if the wal-sender - can intercept such toasted values and translate it to actual values before being sent - that would solve the problem without every consumer having to implement a solution to handle toasted value. This would also help prevent the unwarranted increase of the wal-log if replica-identity to full is set as a w/a. Any thoughts? Thanks.
Re: psql and pgpass.conf on Windows
On Thu, 29 Jun 2023 20:27:59 -0700 David G. Johnston wrote: >On Thu, Jun 29, 2023 at 7:42 PM wrote: > >> Trying to write a script that will run on Linux, Windows, and Mac. >> > >This seems impossible on its face unless you use WSL within the Windows >environment. And if you are doing that, then the pathing would be WSL >pathing, not native Windows. >David J. Sorry, a Python script which has support for each platform. We have scripts which run on all; the real question is why is psql not finding/using pgpass.conf... (see my reply to Julien Rouhaud) Regards, Pierre
Re: psql and pgpass.conf on Windows
On Fri, 30 Jun 2023 11:16:36 +0800 Julien Rouhaud wrote: >Hi, > >On Thu, Jun 29, 2023 at 10:42:00PM -0400, p...@pfortin.com wrote: >> >> Windows: %APPDATA%\postgresql\pgpass.conf >> >> On Linux, this works. However, on Windows, psql will not read >> pgpass.conf (tried in just about every location I could think of) >> >> Even: "set PGPASSFILE=" does not work. >> >> Finally, out of frustration, tried: >> set PGPASSWORD= >> and that got me past the password issue, only to now get: >> 'more' is not recognized as an internal or external command, >> operable program or batch file. >> >> Given the number of queries about pgpass.conf and finding no answer that >> works, is there no bug report on this? >> >> Thinking that psql was not adjusted for Windows, tried naming the file: >> .pgpass >> .pgpass.conf >> also in various locations to no avail... >> What am I (and all the others found in searches) missing? Or are there >> unresolved bugs in psql? >>- pgpass.conf >>- expecting external executable: 'more' > >The file should indeed be by default %APPDATA%/postgresql/pgpass.conf, Will have to check with the owner of that machine where the PG install came from... maybe a non-official installer...? >and it's known to be functional on Windows. Given the number of searches we've done, at least one "Success" message should have been found; alas... >The fact that you hit some error with a "more" program makes me think that your >script setup some environment variables (like PAGER=more, which would explain >why you hit that error) that maybe interfere with file location and/or name. Good point; but... echo %PAGER% %PAGER% (does Windows normally print var name if empty? I'm a Linux-only user since 1998) ...apparently, it does: echo %JUNK% %JUNK% ;p vs: echo %APPDATA% C:\Users\Pierre\AppData\Roaming >Now, since setting PGPASSFILE also doesn't work I start to wonder if there's >another problem. Does the password (or any other field) contain some non-ASCII >characters? type %APPDATA%\postgresql\pgpass.conf 127.0.0.1:5432:ncsbe:postgres: (only ASCII characters and no trailing space) was using "localhost" before trying 127.0.0.1 even "*:*:..." failed. >There could be an encoding issue in the file, or maybe the problem >is with the presence or absence of a BOM in the file. Another thing you should >try just in case is to replace backwards slashes with forward slashes. No [back]slashes at all (except in later testing with PGPASSFILE...but psql should be looking in the right place without hinting... >If none of that work, you could also check what file psql is trying to open >using the equivalent of "strace" for Windows, if such a thing exists. LOL Knowing it wouldn't work, I actually typed "strace"... to see if psql was accessing the file, I tried changing the port number; but psql still mentions 5432 in its error message. Thanks, will do more digging when I return, Pierre
Re: Query regarding managing Replication
Hi, On Fri, Jun 30, 2023 at 01:47:29PM +0530, Ashok Patil wrote: > Hello MAZIÈRE, > > I tried update by > > listen_addresses = '*' and listen_addresses = 'server_address' but still > i am getting same error. Did you restart postgres after changing listen_addresses, and is the primary port 5432? If yes and if you still get the same connection errors, it mean that either the primary IP is wrong or something is blocking the traffic between the two machines (like a firewall).
Re: Query regarding managing Replication
Hello MAZIÈRE,
I tried update by
listen_addresses = '*' and listen_addresses = 'server_address' but still
i am getting same error.
Searched on internet for probable solution but does not get any proper
answer.
Will you please reply me for this issue?
Regards,
Ashok
On Tue, Jun 27, 2023 at 7:39 PM Romain MAZIÈRE <
romain.mazi...@sigmaz-consilium.fr> wrote:
> Hello,
>
> You can have a look at the parameter : listen_addresses in the file
> postgresql.conf.
> By default the value is localhost.
>
> Regards
>
> Romain MAZIÈreromain.mazi...@sigmaz-consilium.fr
> +33.535.545.085
> +33.781.46.36.96https://sigmaz-consilium.fr
>
> Le 27/06/2023 à 16:04, Ashok Patil a écrit :
>
> Hello Sir/Madam,
>
> I have to perform task of database replication. For that for testing
> purpose I have installed Postgress 14 on two different machine. One is
> primary (We can say it as server) and another one is secondary (stand by).
>
> Below steps i have performed
>
> *On Primary*
>
> 1. Update Postgres.conf with below settings
> wal_level = hot_standby
> full_page_writes = on
> wal_log_hints = on
> max_wal_senders = 6
> max_replication_slots = 6
> hot_standby = on
> hot_standby_feedback = on
>
> 2. update pg_hba.conf
> host replication 172.20.32.63/32 scram-sha-256
> host replication 172.20.32.43/32 scram-sha-256
>
> 3. Create a repl_user
> psql -d postgres -U postgres -c "CREATE ROLE repl_user LOGIN
> REPLICATION ENCRYPTED PASSWORD 'xxx';"
>
> 4. create the replication slot using below command
> psql -d postgres -U postgres -c "SELECT * FROM
> pg_create_physical_replication_slot('standby1', true);"
>
> *On Standby*
>
> 5.Perform a base backup of primary to standby
> pg_ctl -D ..\data. stop -mi
>
> 6. After stopping the cluster delete the data directory
> rmdir /s ..\data
>
> 7. run pg_basebackup on the standby to copy primary’s data directory to it.
> pg_basebackup -D ..\data -Fp -R -Xs -c fast -l 'initial_clone' -P -v -h
> 172.20.32.63 y -U repl_user
>
> but here i am getting error as
> pg_basebackup: error: connection to server at "172.20.32.63", port 5432
> failed: Connection timed out (0x274C/10060)
> Is the server running on that host and accepting TCP/IP
> connections?
>
> Will you please let me know which steps is wrong.
>
> Also is there any proper steps given in any document, if yes, will you
> please share that.
>
> Thanks in advance.
>
> Regards,
> Ashok
>
>
>
