Setting up replication on Windows, v9.4
I'm setting up a backup for our primary postgres server using the archived WAL files. Then I'll try to upgrade it to Streaming Replication. Then I'll upgrade the system to v.latest. For now, we are on v.9.4. I do a base backup from the primary to a directory on the NAS. "C:\Program Files\PostgreSQL\9.4\bin\pg_basebackup.exe" -D \\diskstation\AccessData\Dev\Backup -P -X s -v -h 192.168.1.118 -p 5432 -U postgres That appears to go fine. Then I delete data\*.* and copy everything except the config files from the backup into data. Copy in recovery.conf -- standby_mode = 'on' primary_conninfo = 'host=192.168.1.118 port=5432 user=replication password=**' restore_command = 'copy "DISKSTATION\\AccessData\\WALfiles\\%f" "%p"' -- Copy in postgresql.conf, with settings -- listen_addresses = '127.0.0.1,192.168.1.118' wal_level = archive hot_standby = on -- Interestingly, the recovery file says # Note that recovery.conf must be in $PGDATA directory. # It should NOT be located in the same directory as postgresql.conf Those seem contradictory. And if I remove the postgresql, it just refuses to start. With all this in place, I start the service, it runs for a bit, then shuts down. No errors in the event log. 5 postgres processes are left running along with a pid file. The log file says -- LOG: database system was interrupted while in recovery at log time 2022-11-04 13:17:28 PDT HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target. FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up LOG: entering standby mode LOG: consistent recovery state reached at 6A/3590 LOG: record with zero length at 6A/3590 LOG: started streaming WAL from primary at 6A/3500 on timeline 1 LOG: redo starts at 6A/3590 FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up So it seems that the backup didn't work as well as first appeared.
Re: Setting up replication on Windows, v9.4
> > > > v9.4 has been EOL for 2 years 9 months. As I said, the next step will be to upgrade. It would make sense to upgrade first, since "there have been some big advances since then which make replication much easier" But when we upgraded, the app stopped working. So I'll need to go through and nail down which exact version causes the issue and then see if we can get it resolved. I have no way of knowing how long that will take. So we're doing the replication first.
Re: Setting up replication on Windows, v9.4
On 11/7/2022 3:42 PM, Rob Sargent wrote: > Care to share some of the ways the app stopped working? You might get a leg up on where best to remediate. I don't recall, as that was a few months ago. We are running MS-Access as a front end with Postgres as the back end. It appeared to read and write data perfectly fine, but failed in the business logic on the Access side where there didn't appear to be any DB involvement. But switching to v14 caused it and switching away fixed it. No obvious cause that I could see. As written, switching databases was very time-consuming. Since then, I've rewritten the app so that the network admin just changes an INI file that the app reads on start up. If there is a change, it switches all the tables over in about 15 seconds. We wanted that ability anyway in case we ever needed to switch to using the backup server. Now, I am working on getting replication going. Next up: back to upgrading. But, since you ask, I'll post here when I get back to that so y'all can follow along as I narrow it down to a specific version.
Re: Setting up replication on Windows, v9.4
From the error log: FATAL: hot standby is not possible because wal_level was not set to "hot_standby" or higher on the master server HINT: Either set wal_level to "hot_standby" on the master, or turn off hot_standby here. I tried setting hot_standby to off, but that didn't help. I set the wal_level to "hot_standby" and hot_standby to on, and now it appears to have made progress. The service starts up without failing. > check if there's an entry in pg_stat_replication on the primary. No. No entries. There is now an entry on the primary. But still can't connect from Navicat on the primary server and the log says FATAL: could not receive data from WAL stream: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. LOG: record with zero length at 6B/6390 FATAL: could not connect to the primary server: FATAL: the database system is starting up LOG: started streaming WAL from primary at 6B/6300 on timeline 1 LOG: startup process (PID 3368) exited with exit code 1 LOG: terminating any other active server processes ¯\_(ツ)_/¯
Re: Setting up replication on Windows, v9.4
> Or manually running the application queries? That was the odd thing. It didn't appear to be in the middle of running any queries. The database shouldn't have had any effect. > Anyway, try Pg 9.6. I have Pg 12 installed, so I'll try that. I'll start a new thread on my adventures in upgrading. Thanks, Brad. On Fri, Nov 11, 2022 at 12:38 PM Ron wrote: >Nothing in the application error logs? What about the Postgresql error > logs? Or manually running the application queries? > > Anyway, try Pg 9.6. Still EOL, but *not as* EOL. > > -- > Angular momentum makes the world go 'round. >
Upgrading to v12
I'm upgrading from v9.4 to v12.10 as a half step to 15.
Q1: How do I tell it which database to upgrade?
I only need the primary.
Not the half dozen restored copies.
Or do I need to detach everything I don't want copied?
Q2: I get this error, and then at the end, it says "No error."
Performing Consistency Checks
-
Checking cluster versions ok
SQL command failed
WITH regular_heap (reloid, indtable, toastheap) AS ( SELECT c.oid,
0::oid, 0::oid FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n
ON c.relnamespace = n.oid WHERE relkind IN ('r', 'm') AND
((n.nspname !~ '^pg_temp_' AND n.nspname !~ '^pg_toast_temp_' AND
n.nspname NOT IN ('pg_catalog', 'information_schema',
'binary_upgrade', 'pg_toast') AND c.oid >= 16384::pg_catalog.oid)
OR (n.nspname = 'pg_catalog' AND relname IN ('pg_largeobject')
))), toast_heap (reloid, indtable, toastheap) AS ( SELECT
c.reltoastrelid, 0::oid, c.oid FROM regular_heap JOIN pg_catalog.pg_class
c ON regular_heap.reloid = c.oid WHERE c.reltoastrelid != 0),
all_index (reloid, indtable, toastheap) AS ( SELECT indexrelid, indrelid,
0::oid FROM pg_catalog.pg_index WHERE indisvalid AND indisready AND
indrelid IN (SELECT reloid FROM regular_heap UNION ALL
SELECT reloid FROM toast_heap)) SELECT all_rels.*, n.nspname,
c.relname, c.relfilenode, c.reltablespace,
pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM (SELECT * FROM
regular_heap UNION ALL SELECT * FROM toast_heap UNION ALL
SELECT * FROM all_index) all_rels JOIN pg_catalog.pg_class c
ON all_rels.reloid = c.oid JOIN pg_catalog.pg_namespace n ON
c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_tablespace t ON
c.reltablespace = t.oid ORDER BY 1;
ERROR: could not access status of transaction 22316920
DETAIL: Could not read from file "pg_clog/0015" at offset 73728: No error.
Re: Upgrading to v12
> What was the complete pg_upgrade command you used? "C:\Program Files\PostgreSQL\12\bin\pg_upgrade" -d "C:\Program Files\PostgreSQL\9.4\data" -D "C:\Program Files\PostgreSQL\12\data" -b "C:\Program Files\PostgreSQL\9.4\bin" -B "C:\Program Files\PostgreSQL\12\bin" -U postgres -p 5432 -P 5435 > >
Re: Upgrading to v12
I deleted all the other DBs and left only the primary. Still getting the same error message, ending with ERROR: could not access status of transaction 22316920 DETAIL: Could not read from file "pg_clog/0015" at offset 73728: No error.
Re: Upgrading to v12
I'm practicing on our Dev server, so I can blow this away and reload at any time. Are there any utilities to check for corruption on my Prod server in v9.4.1? All my backups are done with pg_dump.exe, so that's where this database came from in the first place. So we know that pg_dump.exe works on Prod at least. On Fri, Nov 11, 2022 at 3:17 PM Adrian Klaver wrote: > On 11/11/22 13:11, Brad White wrote: > > I deleted all the other DBs and left only the primary. > > Still getting the same error message, ending with > > > > ERROR: could not access status of transaction 22316920 > > DETAIL: Could not read from file "pg_clog/0015" at offset 73728: No > error. > > Can you do a pg_dump of that database? > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Re: Upgrading to v12
> Can you do a pg_dump of that database? Yes. No visible problems. No errors reported. On Fri, Nov 11, 2022 at 3:17 PM Adrian Klaver wrote: > On 11/11/22 13:11, Brad White wrote: > > I deleted all the other DBs and left only the primary. > > Still getting the same error message, ending with > > > > ERROR: could not access status of transaction 22316920 > > DETAIL: Could not read from file "pg_clog/0015" at offset 73728: No > error. > > Can you do a pg_dump of that database? > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Re: Upgrading to v12
> From your original post, what did "Not the half dozen restored copies" mean? Over time, we've restored multiple copies for testing and reproducing various issues. I'm only trying to set up replication one one of those copies. > In other words define the restore process. Command to back up the database: "C:\Program Files\PostgreSQL\9.4\bin\pg_dump.exe" --host localhost --port 5432 --username "postgres" --no-password --format custom --blobs --verbose --file "DB_"%datestr%"\DB_"%datestr%.backup "DB" Restore: $pgdir\pg_restore.exe -h localhost -p 5433 -U postgres --no-password --clean --if-exists --format=custom --dbname="DB_test" "C:\Temp\DB_20220922_2300\DB_20220922_2300.backup" The errors looked insignificant to me. The database comes up and appears to be perfectly functional. pg_restore.exe : pg_restore: while INITIALIZING: At C:\Temp\Restore12.ps1:36 char:2 + &$prestore -h $phost -p $pport -U postgres --no-password --clean --if-exists -- ... + + CategoryInfo : NotSpecified: (pg_restore: while INITIALIZING::String) [], RemoteException + FullyQualifiedErrorId : NativeCommandError pg_restore: error: could not execute query: ERROR: unrecognized configuration parameter "idle_in_transaction_session_timeout" Command was: SET idle_in_transaction_session_timeout = 0; (Note: This is a backup from 9, being restored to 12. I assume that pg_restore: error: could not execute query: ERROR: unrecognized configuration parameter "row_security" Command was: SET row_security = off; pg_restore : WARNING: column "Button2" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "Button3" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore : WARNING: column "Button4" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore: WARNING: column "Button5" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore : WARNING: column "Button6" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore : WARNING: column "Button7" has type "unknown" DETAIL: Proceeding with relation creation anyway. pg_restore : warning: errors ignored on restore: 2 On Fri, Nov 11, 2022 at 3:42 PM Brad White wrote: > I'm practicing on our Dev server, so I can blow this away and reload at > any time. > Are there any utilities to check for corruption on my Prod server in > v9.4.1? > > All my backups are done with pg_dump.exe, so that's where this database > came from in the first place. > So we know that pg_dump.exe works on Prod at least. > > On Fri, Nov 11, 2022 at 3:17 PM Adrian Klaver > wrote: > >> On 11/11/22 13:11, Brad White wrote: >> > I deleted all the other DBs and left only the primary. >> > Still getting the same error message, ending with >> > >> > ERROR: could not access status of transaction 22316920 >> > DETAIL: Could not read from file "pg_clog/0015" at offset 73728: No >> error. >> >> Can you do a pg_dump of that database? >> >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> >>
Re: Upgrading to v12
Sorry. Ignore the errors. That was mistakenly copied in from elsewhere.
Re: Upgrading to v12
On Fri, Nov 11, 2022, 9:57 PM Adrian Klaver wrote: > On 11/11/22 18:41, Brad White wrote: > > > From your original post, what did "Not the half dozen restored copies" > > mean? > > Over time, we've restored multiple copies for testing and reproducing > > various issues. > > > > I'm only trying to set up replication one one of those copies. > > > > > In other words define the restore process. > > > > Command to back up the database: > > "C:\Program Files\PostgreSQL\9.4\bin\pg_dump.exe" --host localhost > > --port 5432 --username "postgres" --no-password --format custom --blobs > > --verbose --file "DB_"%datestr%"\DB_"%datestr%.backup "DB" > > > > Restore: > > $pgdir\pg_restore.exe -h localhost -p 5433 -U postgres --no-password > > --clean --if-exists --format=custom --dbname="DB_test" > > "C:\Temp\DB_20220922_2300\DB_20220922_2300.backup" > > Alright I am confused. You said you had multiple copies of the database > on one cluster. yes. They've been deleted now, but we did. The above though shows you restoring to different > cluster(5433) then the cluster(5432) you dumped from. > Yes. The backup is from production. V9.4 is running on 5432 on all servers. That particular restore happens to be on the dev server. 5433 is v12. > > Also why > > "C:\Program Files\PostgreSQL\9.4\bin\pg_dump.exe > > vs > > $pgdir\pg_restore.exe > ? > > Is pgdir different from "C:\Program Files\PostgreSQL\9.4\bin\ ? > "C:\Program Files... is from the backup script. Production is always only on one version. $pgdir is from the restore script. So it might be PostgreSQL\9.4\bin or PostgreSQL\12\bin or PostgreSQL\15\bin Turns out that it doesn’t work well to mix the exe from one and the port from another. > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Re: Upgrading to v12
> When moving up in version you need to use the newer version of pg_dump(...\12\bin\pg_dump.exe) to dump the 9.4 instance and then the version 12 pg_restore to the 12 instance. Oh my. That's a substantial change that could make a difference. Thanks for catching that. > >
Re: Upgrading to v12
> Step #1: upgrade to 9.4.26. You'll get *five years* of bug fixes. Good idea. I'll try 12 first, and if that doesn't work we'll go with this. >
Re: Upgrading to v12
> If the client lets you, of course. Right? 8: -) That's not a concern here. A) They trust me, and B) They only see the front end. They don't really care what happens with the back end. so long as A) It doesn't break, and B) We get replication working. >
Re: Upgrading to v12
> > > > How where the restored copies made on the original cluster? > I guess I'm not understanding the confusion here. They were restored with > the same script but to a different DB name and with the 9.4 executables. > In fact, that was why the script was originally written, so we could > restore and test the backups. > > I've since hijacked it and used it to restore to other versions. >
Re: Upgrading to v12
tl;dr How do I turn up the logging so I can see what is failing? In our quest to get replication working, we are upgrading from v9.4 to v12.10. Access365 via ODBC Driver = "PostgreSQL Unicode" v13.02, Date 9/22/2021 In testing the app against v12, I find this issue: On updating a record, I set values on several fields and call recordSet.Update after each one. After updating one particular field, calling Update gives --> The Microsoft Access database engine stopped the process because you and another user are attempting to change the same data at the same time. Code in question: rst!Update <-- success rst!QtyDeliverable = rst!Quantity rst.Update <-- fails here The wisdom of the internet says that this is most likely with a BIT field that has null that Access can't handle. But that isn't the case here. Both are int4 fields and both have values before the update. For context, this is after we've copied the order record. Then we copy this Order Item record. Then we copy all the child records. Lastly, we are updating a few fields in this Order Item record and the update fails. This only fails against PostgreSQL 12.10, compiled by Visual C++ build 1914, 64-bit Succeeds against PostgreSQL 9.4.1, compiled by Visual C++ build 1800, 64-bit I don't see anything in the data\logs folder that looks relevant. Where else should I look? >
Re: Upgrading to v12
On 11/18/2022 6:34 PM, Adrian Klaver wrote: On 11/18/22 16:05, Brad White wrote: --> The Microsoft Access database engine stopped the process because you and another user are attempting to change the same data at the same time. Code in question: rst!Update <-- success rst!QtyDeliverable = rst!Quantity rst.Update <-- fails here The wisdom of the internet says that this is most likely with a BIT field that has null that Access can't handle. But that isn't the case here. Both are int4 fields and both have values before the update. The new PostgreSQL timestamp data type defaults to microsecond precision. This means that timestamp values are stored like 2002-05-22 09:00:00.123456-05. However, Access does not support the extra precision, so the value that Access uses is 2002-05-22 09:00:00-05. When one tries to update a record, one gets the error message above because the value that Access uses in its UPDATE query does not match the value in the PostgreSQL table, similar to the NULL vs. empty string conflict that is already reported in this FAQ entry. " The above is the problem I usually ran into with Access and Postgres and updating. Is there a timestamp field in the record you are updating? UPDATE: Yes, there are 5 timestamp fields. It seems unlikely to be the culprit for 3 reasons. 1) It worked fine in v9.4 2) It worked the previous 4 times I saved that record in v12. 3) As the data came from Access, there is no data in any of the fields in the last three decimal places. ex. 45.234000 But as it is the best lead I have, and it could still be the culprit until proven otherwise, I'm working to convert those 5 fields from timestamp to timestamp(3). Of course, PG doesn't allow to edit a table with dependent views. Which means that I'm attempting to modify a script that will allow me to save, drop, restore the views. Of course, PG coerces all table and field names to lowercase unless quoted. So I have to figure how to recognize all table names and add quotes. This table is core to the app, so a LOT of the views reference it. I may not be done anytime soon. Have a good vacation!
Re: Upgrading to v12
Tom, I tried to run initdb after re-installing pg 12 using postgresql-12.10-2-windows-x64.exe. But the runas I'm using to execute it as pguser seems to be swallowing all the output, so I can't see any errors. I was able to run pg_checksums and get those enabled. Is there anything else I want from initdb? Or can I skip that? On Fri, Nov 11, 2022 at 4:27 PM Tom Lane wrote: > Nonetheless, > your path forward is clear: use pg_dump (or better pg_dumpall) > and then load the output into a *freshly initdb'd v12 installation.* > It'll be a bit slower than the pg_upgrade way, but it'll work. > > regards, tom lane >
Re: postgresql 13.1: precision of spatial operations
On 11/30/2022 9:48 AM, Вадим Самохин wrote: Thank you so much Ivan, it worked! Can you give any more detail on which approach you took, for the sake of future followers?
Re: Stored procedure code no longer stored in v14 and v15, changed behaviour
On 12/2/2022 9:05 AM, Peter J. Holzer wrote: I don't know how reproducable that tokenization process is. Can you just do it again and compere the results? Right. You can compare passwords, even though you don't store the original. You might be able to run your virgin copy through the tokenization and compare the results. -- Quote Signature I talk with clients, find out where their pain points are, and solve those. On-call IT Management for small companies and non-profits. SCP, Inc. bwh...@inebraska.com 402-601-7990 Quote of the Day There is a huge difference between fathering a child and being a father. One produces a child. The other produces an adult. -- John Eldredge
Selecting across servers
I needed to be able to compare the contents of a table across
several databases and clusters.
Since it took me several hours to write this, I thought I'd share it with
you, in case anyone needs a starting point for anything similar.
BACKGROUND DETAILS:
These databases are all on sandbox servers, restored backups from
production.
We are querying the most recent entry in the log table. You'll see that
some have a date of 11/1 (from the backup) and some have a more recent
date. This is a problem as I've run the client against all the databases.
We conclude that the log entries are not all going to the "current"
database. I needed an easy way to see where they *were* going.
IMPLEMENTATION DETAILS:
In this case, all the databases are on the same server and the same DB name
but different ports. But you can obviously modify the connect string to hit
any combination you need.
This assumes a view exists with the same name on each database.
'LatestLogEntry' in our case.
As you'll see in the results, we are running
V9.4 on port 5432
V10 on 5433
V11 on 5434
V12 on 5435
It raises a NOTICE at the end to print out the query just for debugging
purposes.
Here is the text for LatestLogEntry
-- retrieve the most recent log entry
SELECT current_setting('port'::text) AS "Port",
current_setting('server_version'::text) AS "Version",
"System Log"."AddDate"
FROM "System Log"
ORDER BY "System Log"."AddDate" DESC
LIMIT 1
And the text for our routine to retrieve results from across clusters:
CREATE EXTENSION IF NOT EXISTS dblink;
BEGIN;
DO
$$
DECLARE
conn_template TEXT;
conn_string9 TEXT;
conn_string10 TEXT;
conn_string11 TEXT;
conn_string12 TEXT;
_query TEXT;
_cursor CONSTANT refcursor := '_cursor';
BEGIN
conn_template = 'user={user} password={password} dbname={DB} port=';
conn_string9 = conn_template || 5432;
conn_string10 = conn_template || 5433;
conn_string11 = conn_template || 5434;
conn_string12 = conn_template || 5435;
_query := 'select "Port", "Version", "AddDate" from dblink(''' ||
conn_string9 || ''', ''(select * from "LatestLogEntry")'') as t1("Port"
integer, "Version" text, "AddDate" timestamp)' ||
' UNION select "Port", "Version", "AddDate" from dblink(''' ||
conn_string10 || ''', ''(select * from "LatestLogEntry")'') as t1("Port"
integer, "Version" text, "AddDate" timestamp)' ||
' UNION select "Port", "Version", "AddDate" from dblink(''' ||
conn_string11 || ''', ''(select * from "LatestLogEntry")'') as t1("Port"
integer, "Version" text, "AddDate" timestamp)' ||
' UNION select "Port", "Version", "AddDate" from dblink(''' ||
conn_string12 || ''', ''(select * from "LatestLogEntry")'') as t1("Port"
integer, "Version" text, "AddDate" timestamp) ORDER BY "Port";';
OPEN _cursor FOR EXECUTE _query;
RAISE NOTICE '%', _query;
END
$$;
FETCH ALL FROM _cursor ;
COMMIT;
Results:
Port Version AddDate
5432 9.4.1 2022-12-09 16:44:08.091
5433 10.20 2022-11-01 17:01:33.322
5434 11.15 2022-12-16 12:43:31.679973
5435 12.10 2022-11-01 17:01:33.322
Re: Implementing foreign data wrappers and avoiding n+1 querying
We had a similar situation in a completely different context. Our eventual solution was to fire off a request as soon as one came in. Then we batched further requests until the first returned. Whenever a request returned, we sent any pending requests. Any single request not sent immediately was slowed slightly, but overall the system was faster because of the reduced traffic. Brad On Thu, Dec 22, 2022, 6:51 AM David Gilman wrote: > I apologize that my post was not super clear, I am thinking about > implementing a fdw from scratch, and the target database is one of those > NoSQL databases where you have to send JSON over a HTTP connection for each > query. > > I have reviewed the postgres fdw code to see how it works and to see > what's possible. Although it probably wouldn't benefit as much from this > sort of thing (yay to postgres' design!) It could possibly still benefit a > bit, which makes me wonder if it can't be done with the current planner > nodes it might be a worthy improvement to add support for this. > > On Wed, Dec 21, 2022, 10:57 PM David Rowley wrote: > >> On Thu, 22 Dec 2022 at 13:31, David Gilman >> wrote: >> > >> > When a fdw table participates in query planning and finds itself as >> > part of a join it can output a parameterized path. If chosen, Postgres >> > will dutifully call the fdw over and over via IterateForeignScan to >> > fetch matching tuples. Many fdw extensions do network traffic, though, >> > and it would be beneficial to reduce the total number of queries done >> > or network connections established. >> >> Sounds like you might be looking for fdw_startup_cost [1]. >> >> David >> >> [1] https://www.postgresql.org/docs/current/postgres-fdw.html >> >
Re: How to write a crosstab which returns empty row results
On 12/24/2022 9:03 PM, David Goldsmith wrote: How do I force "empty rows" to be included in my query output? (I've tried LEFT JOINing to the row header results, and using CASE statements; but due to my unfamiliarity w/ using crosstab, I'm not sure if I've used those correctly in the current context; Can you give us a head start by showing the query you have now that is not working. Extra points if you give simple create/populate statements that demonstrate the problem. Hope that helps, Brad.
Updating column default values in code
RECAP I'm running an Access front end against the Postgres back end. Copying and updating a record succeeds in 9.4 but fails in 9.5 and everything after. It was the precision of the timestamp fields after all. Turns out the initial data wasn't coming from Access, but from the field default value of "now()" They must have added additional checking between 9.4 and 9.5.8: -) I added code to set the default values for the 5 timestamp fields and now it works correctly. I'm only a third of the way through the schema and I already have 30 tables with the same default which need to be updated. Trying to find everywhere a record is added in code seems error-prone, so I want to stay with the current approach of using the column default. PROBLEM: On timestamp fields, I need to update the column default from the current "Now()" to "LOCALTIMESTAMP(0)" I could just manually make the change on every table, but then we would still fail if we ever needed to restore a database. So I need something that I can build into my Powershell restore script. I've gotten the list of columns and, I think, table IDs. How do I update the relation? SELECT a.attrelid, a.attname, pg_get_expr(d.adbin, d.adrelid) AS default_value FROM pg_catalog.pg_attribute AS a JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, d.adnum) WHERE NOT a.attisdropped -- no dropped (dead) columns ANDa.attnum > 0 -- no system columns AND pg_get_expr(d.adbin, d.adrelid) = 'now()' returns 95 rows like attrelid attname default_value 16398AddDate now() 16407AddDate now() 16421AddDate now() 16433Deposit_Date now() 16433ArchDate now() 16473AddDate now()
Re: REINDEX vs VACUUM
On 1/4/2023 9:34 AM, Ron wrote: I don't think VACUUM FULL (copy the table, create new indices and other metadata all in one command) actually vacuums tables. It's a misleading name. Something like REBUILD TABLE would be a better name. Well s***. That explains a lot. Thanks for clearing that up for me.
Re: Updating column default values in code
On Wednesday, December 28, 2022, Brad White wrote: > On timestamp fields, I need to update the column default from the current > "Now()" to "LOCALTIMESTAMP(0)" > > I could just manually make the change on every table, but they want the > existing backups to still work. So I need something that I can build into > my restore script. > > I've gotten the list of tables and columns. > > How do I update the relation? > > SELECT a.attrelid, a.attname, pg_get_expr(d.adbin, d.adrelid) AS > default_value > FROM pg_catalog.pg_attribute AS a > JOIN pg_catalog.pg_attrdef d ON (a.attrelid, a.attnum) = (d.adrelid, > d.adnum) > WHERE NOT a.attisdropped -- no dropped (dead) columns > ANDa.attnum > 0 -- no system columns > AND pg_get_expr(d.adbin, d.adrelid) = 'now()' > > > Extend the query so all inputs needed to manually write an ALTER TABLE command (chiefly, you need textual names, not oids). Then use format() to actually write those commands using the query as input. You can use psql \gexec to actually execute those queries, though other options exist depending on what tools you are comfortable with). David J. I'm still suffering here. I have 95 column defaults to update across most of my data tables. I can generate ALTER statements, per David's sensible suggestion, but they fail because you have to drop all the views. I created a script that would drop and re-create all the views, but all the table/column names are non-quoted which fails because all my tables/columns are mixed-case. So I either need to -- generate a script to re-create the views that works, -- or parse my script, recognize all the relation names, force them to proper casing, and wrap in quotes, so I can drop and regenerate the views properly, -- or alter the definition of the column defaults inplace in pg_catalog.pg_attrdef. Altering the defaults seems safe because the default value shouldn't affect the view at all. Thanks for any suggestions, Brad.
Re: Updating column default values in code
On 1/6/2023 7:44 PM, Ken Tanzer wrote: On Fri, Jan 6, 2023 at 3:32 PM Brad White wrote: I can generate ALTER statements, per David's sensible suggestion, but they fail because you have to drop all the views. Altering the defaults seems safe because the default value shouldn't affect the view at all. Are you sure those alter statements will fail? I do that frequently. Plus would expect it to work because of your second statement. Here's an example, running on 9.6: CREATE TABLE foo (f1 integer DEFAULT 1, f2 integer); CREATE VIEW foo_view AS SELECT * FROM foo; ALTER TABLE foo ALTER COLUMN f1 SET DEFAULT 3; ALTER TABLE foo ALTER COLUMN f2 SET DEFAULT 2; agency=> BEGIN; BEGIN agency=> CREATE TABLE foo (f1 integer DEFAULT 1, f2 integer); CREATE TABLE agency=> CREATE VIEW foo_view AS SELECT * FROM foo; CREATE VIEW agency=> ALTER TABLE foo ALTER COLUMN f1 SET DEFAULT 3; ALTER TABLE agency=> ALTER TABLE foo ALTER COLUMN f2 SET DEFAULT 2; ALTER TABLE Cheers, Ken -- That's a good point. It was failing when I was trying to change the field type, before I stumbled on the real issue of the default values. I realize now that I assumed and didn't try to update just the defaults. Thanks, Brad. -- Quote Signature I talk with clients, find out where their pain points are, and solve those. On-call IT Management for small companies and non-profits. SCP, Inc. bwh...@inebraska.com 402-601-7990 Quote of the Day There is a huge difference between fathering a child and being a father. One produces a child. The other produces an adult. -- John Eldredge
Re: Best Open Source OS for Postgresql
On 1/31/2023 6:23 AM, hubert depesz lubaczewski wrote: Not sure why: 2. you ask me that off list In a lot of email clients, it is very easy to accidentally reply to the author instead of the list without realizing it. Hope that helps, Brad. -- Quote Signature I talk with clients, find out where their pain points are, and solve those. On-call IT Management for small companies and non-profits. SCP, Inc. bwh...@inebraska.com 402-601-7990 Quote of the Day There is a huge difference between fathering a child and being a father. One produces a child. The other produces an adult. -- John Eldredge
Re: PostgreSQL
I'm setting my options for my DNS-less connection using https://odbc.postgresql.org/docs/config.html and https://odbc.postgresql.org/docs/config-opt.html I found it interesting that the sample at the bottom of the first page had options that don't exist on the second page. Namely, B4, B5, D4. I hope those are not important. The second page has options that are nowhere mentioned on the first page, and vice-versa. "Recognize unique indexes" is there as "UniqueIndex", but no indication of the proper keyword to include it in the connection string. CX - Abbreviate doesn't seem to have a corresponding entry in the descriptions on page 1, and from some of the samples I found, seems like a bit field, but no indication of possible options to use. And lastly, one of the ODBC dialog boxes I saw had a checkbox for Keyset query optimization, but no discussion on either page. Same with Distributed Transaction settings. Seems like, in an ideal world, there would be one page with the description and that description would include the keyword and possible options for each setting. Thanks, Brad.
Re: A Small psql Suggestion
Front end: Access 365
Back end: Postgres 9.4
(I know, we are in the process of upgrading)
I'm getting some cases where the SQL sent from MS-Access is failing.
Looking at the postgres log shows that the field names and table names are
not being quoted properly.
It has been my experience that Access usually does a better job at
converting the queries than I would have expected, but not in this instance.
For example
Access: connection.Execute "UPDATE [" & strTable & "] SET [" & strTable &
"].[InsertFlag] = Null" _
& " WHERE ((([" & strTable & "].[InsertFlag])=" & lngCurrUID & "));", ,
adCmdText Or adExecuteNoRecords
Note that InsertFlag is bracketed the same way in both instances.
PSQL: UPDATE "public"."Orders" SET InsertFlag=NULL WHERE ("InsertFlag" =
166 )
Note that InsertFlag is quoted once but not the other time.
Of course this gives the error: column "insertflag" of relation "Order
Items" does not exist at character 35
Any suggestions on where to look?
Thanks,
Brad.
Quoting issue from ODBC
Front end: Access 365
Back end: Postgres 9.4
(I know, we are in the process of upgrading)
I'm getting some cases where the SQL sent from MS-Access is failing.
Looking at the postgres log shows that the field names and table names are
not being quoted properly.
It has been my experience that Access usually does a better job at
converting the queries than I would have expected, but not in this instance.
For example
Access: connection.Execute "UPDATE [" & strTable & "] SET [" & strTable &
"].[InsertFlag] = Null" _
& " WHERE ((([" & strTable & "].[InsertFlag])=" & lngCurrUID & "));", ,
adCmdText Or adExecuteNoRecords
Note that InsertFlag is bracketed the same way in both instances.
PSQL: UPDATE "public"."Orders" SET InsertFlag=NULL WHERE ("InsertFlag" =
166 )
Note that InsertFlag is quoted once but not the other time.
Of course this gives the error: column "insertflag" of relation "Orders"
does not exist at character 35.
Looks like I have about 16 unique instances of statements not being quoted
correctly resulting in over 500 errors in the log for today.
Any suggestions on where to look?
Thanks,
Brad.
Re: PostgreSQL
Sorry about the reply failure. I was trying to start a new thread and failed. On Tue, Feb 7, 2023 at 5:42 PM Brad White wrote: > I'm setting my options for my DNS-less connection using > https://odbc.postgresql.org/docs/config.html > and > https://odbc.postgresql.org/docs/config-opt.html > > I found it interesting that the sample at the bottom of the first page had > options that don't exist on the second page. > Namely, B4, B5, D4. I hope those are not important. > > The second page has options that are nowhere mentioned on the first page, > and vice-versa. > > "Recognize unique indexes" is there as "UniqueIndex", but no indication of > the proper keyword to include it in the connection string. > > CX - Abbreviate doesn't seem to have a corresponding entry in the > descriptions on page 1, and from some of the samples I found, seems like a > bit field, but no indication of possible options to use. > > And lastly, one of the ODBC dialog boxes I saw had a checkbox for Keyset > query optimization, but no discussion on either page. > Same with Distributed Transaction settings. > > Seems like, in an ideal world, there would be one page with the > description and that description would include the keyword and possible > options for each setting. > > Thanks, > Brad. >
Re: A Small psql Suggestion
1) Do not tack on to an existing thread, create a new post. Yup. Failure on my part. I'll repost.
Opportunity for clarification on ODBC options
I'm setting my options for my DNS-less connection using https://odbc.postgresql.org/docs/config.html and https://odbc.postgresql.org/docs/config-opt.html I found it interesting that the sample at the bottom of the first page had options that don't exist on the second page. Namely, B4, B5, D4. I hope those are not important. The second page has options that are nowhere mentioned on the first page, and vice-versa. "Recognize unique indexes" is there as "UniqueIndex", but no indication of the proper keyword to include it in the connection string. CX - Abbreviate doesn't seem to have a corresponding entry in the descriptions on page 1, and from some of the samples I found, seems like a bit field, but no indication of possible options to use. And lastly, one of the ODBC dialog boxes I saw had a checkbox for Keyset query optimization, but no discussion on either page. Same with Distributed Transaction settings. Seems like, in an ideal world, there would be one page with the description and that description would include the keyword and possible options for each setting. Hope that helps, Brad.
Fwd: Quoting issue from ODBC
Front end: Access 365
Back end: Postgres 9.4
(I know, we are in the process of upgrading)
I'm getting some cases where the SQL sent from MS-Access is failing.
Looking at the postgres log shows that the field names and table names are
not being quoted properly.
It has been my experience that Access usually does a better job at
converting the queries than I would have expected, but not in this instance.
For example, this is the literal code in VBA
Access: connection.Execute "UPDATE [" & strTable & "] SET [" & strTable &
"].[InsertFlag] = Null" _
& " WHERE ((([" & strTable & "].[InsertFlag])=" & lngCurrUID & "));", ,
adCmdText Or adExecuteNoRecords
Note that InsertFlag is bracketed the same way in both instances.
PSQL: UPDATE "public"."Orders" SET InsertFlag=NULL WHERE ("InsertFlag" =
166 )
Note that InsertFlag is quoted once but not the other time.
Of course this gives the error: column "insertflag" of relation "Orders"
does not exist at character 35.
No ORM involved.
Looks like I have about 16 unique instances of statements not being quoted
correctly resulting in over 500 errors in the log for today.
Any suggestions on where to look?
Thanks,
Brad.
Re: Quoting issue from ODBC
On 2/7/2023 6:19 PM, Adrian Klaver wrote:
On 2/7/23 16:10, Brad White wrote:
Front end: Access 365
Back end: Postgres 9.4
(I know, we are in the process of upgrading)
I'm getting some cases where the SQL sent from MS-Access is failing.
Looking at the postgres log shows that the field names and table
names are not being quoted properly.
It has been my experience that Access usually does a better job at
converting the queries than I would have expected, but not in this
instance.
For example
Access: connection.Execute "UPDATE [" & strTable & "] SET [" &
strTable & "].[InsertFlag] = Null" _
& " WHERE ((([" & strTable & "].[InsertFlag])=" & lngCurrUID &
"));", , adCmdText Or adExecuteNoRecords
Note that InsertFlag is bracketed the same way in both instances.
PSQL: UPDATE "public"."Orders" SET InsertFlag=NULL WHERE
("InsertFlag" = 166 )
Note that InsertFlag is quoted once but not the other time.
Of course this gives the error: column "insertflag" of relation
"Orders" does not exist at character 35.
Looks like I have about 16 unique instances of statements not being
quoted correctly resulting in over 500 errors in the log for today.
Where these preexisting queries or where they created today?
These queries are decades old but I don't view this log file very often,
so I don't know how long.
I'll review when I get back on site Thursday and see if I can find any
users that are not getting the error or when it started.
Any suggestions on where to look?
Thanks,
Brad.
--
Quote Signature I talk with clients, find out where their pain points
are, and solve those.
On-call IT Management for small companies and non-profits.
SCP, Inc.
bwh...@inebraska.com
402-601-7990
Quote of the Day
There is a huge difference between fathering a child and being a
father.
One produces a child. The other produces an adult.
-- John Eldredge
Re: Quoting issue from ODBC
On Tue, Feb 7, 2023 at 10:20 PM Brad White wrote:
> On 2/7/2023 6:19 PM, Adrian Klaver wrote:
>
> On 2/7/23 16:10, Brad White wrote:
>
> Front end: Access 365
> Back end: Postgres 9.4
> (I know, we are in the process of upgrading)
>
> I'm getting some cases where the SQL sent from MS-Access is failing.
> Looking at the postgres log shows that the field names and table names are
> not being quoted properly.
> It has been my experience that Access usually does a better job at
> converting the queries than I would have expected, but not in this
> instance.
>
> For example
>
> Access: connection.Execute "UPDATE [" & strTable & "] SET [" & strTable &
> "].[InsertFlag] = Null" _
> & " WHERE ((([" & strTable & "].[InsertFlag])=" & lngCurrUID & "));",
> , adCmdText Or adExecuteNoRecords
> Note that InsertFlag is bracketed the same way in both instances.
>
> PSQL: UPDATE "public"."Orders" SET InsertFlag=NULL WHERE ("InsertFlag" =
> 166 )
> Note that InsertFlag is quoted once but not the other time.
> Of course this gives the error: column "insertflag" of relation "Orders"
> does not exist at character 35.
>
> Looks like I have about 16 unique instances of statements not being quoted
> correctly resulting in over 500 errors in the log for today.
>
>
> Where these preexisting queries or where they created today?
>
> These queries are decades old but I don't view this log file very often,
> so I don't know how long.
>
> I'll review when I get back on site Thursday and see if I can find any
> users that are not getting the error or when it started.
>
>
>
> Any suggestions on where to look?
>
> Thanks,
> Brad.
>
> Back in the office today and I note that all of the fields that are
getting the issue are the target field in an UPDATE statement.
All the other tables and field names are quoted correctly.
I suspect an ODBC driver bug. Is there a better place to report those?
Driver: PostgreSQL Unicode
Filename: PSQLODBC35W.DLL
Version: 13.02.00
ReleaseDate: 9/22/2021
On the other hand, the app updates things all the time. Only about 12 of
the update statements are ending up in the log. Still looking for the
common denominator in how those statements are called.
ERROR: column "*commitrep*" of relation "Order Items" does not exist at
character 35
STATEMENT: UPDATE "public"."Order Items" SET *CommitRep*='jdoe' WHERE
(("OrderFID" = 557837 ) AND ("*CommitRep*" IS NULL ) )
Here is the original query. You can see that [CommitRep] is written the
same way both times in the query, but in the final quoted correctly once
and incorrectly once.
RunADO "CreditRepWithCommit()", "UPDATE [Order Items] SET *[CommitRep]*
= '" & UID & "'" _
& " WHERE [OrderFID] = " & OrderFID & " AND * [CommitRep]* IS NULL;"
A few other samples from the log. Always just the target field of the
UPDATE that is not quoted.
ERROR: column "*availableserialcount*" of relation "Serial Pools" does not
exist at character 36
STATEMENT: UPDATE "public"."Serial Pools" SET *AvailableSerialCount*=143
WHERE ("ID" = 134 )
ERROR: column "*serialnum*" of relation "Order Items" does not exist at
character 35
STATEMENT: UPDATE "public"."Order Items" SET *SerialNum*='205757'
,LastSerCaptureTypeID=2 WHERE ("ID" = 1891128 )
ERROR: column "*issuedate*" of relation "Order Items" does not exist at
character 35
STATEMENT: UPDATE "public"."Order Items" SET *IssueDate*='2023-02-09
14:28:09'::timestamp ,*IssueRep*=' jdoe ' WHERE ("ID" = 1891128 )
Re: Quoting issue from ODBC
>> Where these preexisting queries or where they created today? > These queries are decades old but I don't view this log file very often, so I don't know how long. > I'll review when I get back on site Thursday and see if I can find any users that are not getting the error or when it started. Going back to early 2020, I don't have any logs that don't have these errors, so it is not a recent change. I don't yet see anything that is user specific. Brad. >
Re: Quoting issue from ODBC
On Thu, Feb 9, 2023 at 5:10 PM Adrian Klaver
wrote:
> On 2/9/23 14:43, Brad White wrote:
> > On Tue, Feb 7, 2023 at 10:20 PM Brad White > <mailto:b55wh...@gmail.com>> wrote:
> >
> > On 2/7/2023 6:19 PM, Adrian Klaver wrote:
> >> On 2/7/23 16:10, Brad White wrote:
> >>> Front end: Access 365
> >>> Back end: Postgres 9.4
> >>> (I know, we are in the process of upgrading)
> >>>
> >>> I'm getting some cases where the SQL sent from MS-Access is
> failing.
> >>> Looking at the postgres log shows that the field names and table
> >>> names are not being quoted properly.
> >>> It has been my experience that Access usually does a better job
> >>> at converting the queries than I would have expected, but not in
> >>> this instance.
> >>>
> >>> For example
> >>>
> >>> Access: connection.Execute "UPDATE [" & strTable & "] SET [" &
> >>> strTable & "].[InsertFlag] = Null" _
> >>> & " WHERE ((([" & strTable & "].[InsertFlag])=" & lngCurrUID
> >>> & "));", , adCmdText Or adExecuteNoRecords
> >>> Note that InsertFlag is bracketed the same way in both instances.
> >>>
> >>> PSQL: UPDATE "public"."Orders" SET InsertFlag=NULL WHERE
> >>> ("InsertFlag" = 166 )
> >>> Note that InsertFlag is quoted once but not the other time.
> >>> Of course this gives the error: column "insertflag" of relation
> >>> "Orders" does not exist at character 35.
> >>>
> >>> Looks like I have about 16 unique instances of statements not
> >>> being quoted correctly resulting in over 500 errors in the log
> >>> for today.
> >>
> >> Where these preexisting queries or where they created today?
> >
> > These queries are decades old but I don't view this log file very
> > often, so I don't know how long.
> >
> > I'll review when I get back on site Thursday and see if I can find
> > any users that are not getting the error or when it started.
> >
> >>
> >>>
> >>> Any suggestions on where to look?
> >>>
> >>> Thanks,
> >>> Brad.
> >
> > Back in the office today and I note that all of the fields that are
> > getting the issue are the target field in an UPDATE statement.
> > All the other tables and field names are quoted correctly.
> >
> > I suspect an ODBC driver bug. Is there a better place to report those?
> >
> > Driver: PostgreSQL Unicode
> > Filename: PSQLODBC35W.DLL
> > Version: 13.02.00
> > ReleaseDate: 9/22/2021
>
> https://www.postgresql.org/list/pgsql-odbc/
>
> >
> > On the other hand, the app updates things all the time. Only about 12 of
> > the update statements are ending up in the log. Still looking for the
> > common denominator in how those statements are called.
>
>
> So how the successful UPDATE's called?
>
I'm still trying to track down all the statements. Because of the
translation between the two database systems, I can't just search on a
simple string.
>
> Are the successful UPDATES's on the same tables and columns?
>
This is the only routine that updates the InsertFlag column. All the order
tables have that flag.
>
> Are these UPDATE's actually necessary?
>
This system is critical to the company, but has a reputation of being
unreliable. I suspect this may be one cause.
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
Re: Quoting issue from ODBC
> Are these UPDATE's actually necessary? > In other words has nobody noticed a problem with the data over that time frame? I don't know what to make of it. I had the same question you did. I now have proof that these hundreds of errors are not an issue. The postgres logs in pg_log clearly show an error, but the errors never show up in the Access log. That means that Access didn't get an error back. Further, I have evidence that the values are actually getting updated. The financial balances are all correct. Issues that were closed are all closed, etc. Even though the Access log says the statement was only run once and Postgres says ERROR, I see no other evidence of it. I can't fathom how that can happen. ¯\_(ツ)_/¯ On the other hand, there are some real errors in the log. I'll put that in another post. Brad
Re: DELETE trigger, direct or indirect?
On 2/16/2023 12:28 PM, Dominique Devienne wrote: On Thu, Feb 16, 2023 at 6:51 PM Adrian Klaver wrote: You have two tables with list in their name, so are rows deleted from both. Just to be clear enity_list should actually be entity_list? Also how are entity and enity_list related? I have to confess that your questions surprise me a bit. I thought the model was pretty clear from the SQL. We have two entities, Foo (my entity table), and another Bar (my entity_list table), with Bar reference 0 or more Foos, recording which ones it references in an association table. Are the values for the name field in entity and enity(entity)_list the same for a given entity? The name of Foo and Bar are completely independent. > transfering the old entity name to it thanks to an UPDATE on > entity_name, thus preserving the old name. How? Again how would you determine where the action started? Deleting a Foo cascades to the _member assoc-table. If the cascade is SET NULL, then I can have an UPDATE TRIGGER on _member. I no longer care where the action started. > Then an INSERT trigger on entity could locate any (indexed) "stashed" > entity names in that extra non-FK column in entity_list_member, How would it locate it if the name that defined the FK(entity(name)) was NULL? In the extra non-FK column I mentioned explicitly, in the _member assoc-table. Another option would be to not delete the records, but add a Deleted column and mark them as deleted. Your CASCADE then becomes an UPDATE trigger. Restoring the relationship would then be a simple matter of unmarking them as deleted. I haven't tried this, but it's possible that you could hijack the DELETE trigger so the app didn't have to change how it deletes records. If you were really insistant on the app not changing to respect the deleted flag, you could add views and read from those. If you potentially have a lot of deleted records, and you have a time frame after which it would be unlikely they would be restored, then you could add a DeletedDate field. After a given amount of time do garbage cleanup on anything over that threshold. We don't have that many deletes, so we just leave them. They don't show up in the app, since they are "deleted" but we have an admin mode that can ignore the deleted flag if the user chooses and they can then undelete any records. So nothing ever gets literally deleted, but they do get archived after 18 months. And again, we have a mode where you can include archived records. In your situation, when they undelete the parent record, it could automatically undelete the children. Brad.
Is Autovacuum running?
I'm concerned that Autovacuum may not be running based on the results of this query. SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables; gives 211 rows like this... *relname| last_vacuum | last_autovacuum*BusinessIncidentCategories | null | null Valid Use | null | null Serial Pool Part Types | null | null BusinessIncidentLog| null | null Rate Categories| null | null I don't see any process with 'auto' or 'vacuum' in the name in TaskManager. I don't see anything similar set up in Services to run in the background. I do see pg_ctl running for each instance of the server running, 9.4 and 14. The settings look ok as far as I can tell. SELECT name, setting FROM pg_settings WHERE name='autovacuum'; name | setting autovacuum | on SELECT name, setting FROM pg_settings WHERE name='track_counts'; name | setting track_counts | on SELECT relname, reloptions FROM pg_class; These are not turned OFF, and I assume the default is ON. *relname | reloptions* pg_statistic | null pg_type | null Activity Codes| null Activity Codes_ID_seq | null .. SELECT * from pg_settings where category like 'Autovacuum'; autovacuum on Autovacuum Starts the autovacuum subprocess. sighup bool default on on autovacuum_analyze_scale_factor 0.1 Autovacuum Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples. sighup real default 0 100 0.1 0.1 autovacuum_analyze_threshold 50 Autovacuum Minimum number of tuple inserts, updates, or deletes prior to analyze. sighup integer default 0 2.15E+09 50 50 autovacuum_freeze_max_age 2E+08 Autovacuum Age at which to autovacuum a table to prevent transaction ID wraparound. postmaster integer default 1E+08 2E+09 2E+08 2E+08 autovacuum_max_workers 3 Autovacuum Sets the maximum number of simultaneously running autovacuum worker processes. postmaster integer default 1 8388607 3 3 autovacuum_multixact_freeze_max_age 4E+08 Autovacuum Multixact age at which to autovacuum a table to prevent multixact wraparound. postmaster integer default 1000 2E+09 4E+08 4E+08 autovacuum_naptime 60 s Autovacuum Time to sleep between autovacuum runs. sighup integer default 1 2147483 60 60 autovacuum_vacuum_cost_delay 20 ms Autovacuum Vacuum cost delay in milliseconds, for autovacuum. sighup integer default -1 100 20 20 autovacuum_vacuum_cost_limit -1 Autovacuum Vacuum cost amount available before napping, for autovacuum. sighup integer default -1 1 -1 -1 autovacuum_vacuum_scale_factor 0.2 Autovacuum Number of tuple updates or deletes prior to vacuum as a fraction of reltuples. sighup real default 0 100 0.2 0.2 autovacuum_vacuum_threshold 50 Autovacuum Minimum number of tuple updates or deletes prior to vacuum. sighup integer default 0 2.15E+09 50 50
Re: Is Autovacuum running?
On Mon, Feb 20, 2023 at 1:42 PM Brad White wrote: > I'm concerned that Autovacuum may not be running based on the results of > this query. > > SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables; > gives 211 rows like this... > *relname| last_vacuum | last_autovacuum* > BusinessIncidentCategories | null | null > Valid Use | null | null > Serial Pool Part Types | null | null > BusinessIncidentLog| null | null > Rate Categories| null | null > > Any suggestions on how to proceed? Thanks, Brad.
Re: Is Autovacuum running?
On Tue, Feb 21, 2023 at 11:58 AM Christophe Pettus wrote: > > > > On Feb 21, 2023, at 09:54, Brad White wrote: > > Any suggestions on how to proceed? > > First, look at pg_stat_user_tables to see how many inserts etc. have > occurred on the tables that are not showing an autovacuum; they may have > simply not reached the threshold yet. If they have, do a VACUUM VERBOSE > across the database and make sure that you can manually vacuum them, or if > it reports that it can't remove tuples. Running the table_bloat_check query from here https://github.com/pgexperts/pgx_scripts/blob/master/bloat/table_bloat_check.sql shows some tables with over 20MB and over 20% bloat while my threshold is set to 0.1. Before tablename | est_rows | pct_bloat | mb_bloat | table_mb Order Items | 169234 | 33| 25.98| 79.695 DocumentLog | 189670 | 23| 5.52 | 23.648 After Order Items | 179860 | 28| 22.6 | 79.695 DocumentLog | 197537 | 23| 5.78 | 24.672 These two now show up as vacuumed in SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables; So at least that table is working. tl;dr Looks to me like vacuum removed 2215 rows from the "Order Items" indexes. "There were 166068 unused item pointers." I don't see anything that looks like an error. [SQL]vacuum verbose "Order Items" INFO: vacuuming "public.Order Items" INFO: scanned index "Order Items_pkey" to remove 2215 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 0.01 sec. INFO: scanned index "Order Items_AuthorityNum" to remove 2215 row versions DETAIL: CPU 0.01s/0.00u sec elapsed 0.14 sec. INFO: scanned index "Order Items_CopySrcID" to remove 2215 row versions DETAIL: CPU 0.01s/0.00u sec elapsed 0.13 sec. INFO: scanned index "Order Items_DelivMethFID" to remove 2215 row versions DETAIL: CPU 0.01s/0.01u sec elapsed 0.12 sec. INFO: scanned index "Order Items_Delivery State FID" to remove 2215 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.09 sec. INFO: scanned index "Order Items_Delivery Zip Code" to remove 2215 row versions DETAIL: CPU 0.01s/0.03u sec elapsed 0.09 sec. INFO: scanned index "Order Items_DiscountFID" to remove 2215 row versions DETAIL: CPU 0.00s/0.03u sec elapsed 0.10 sec. INFO: scanned index "Order Items_EntityWAuthorityFID" to remove 2215 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.06 sec. INFO: scanned index "Order Items_ID" to remove 2215 row versions DETAIL: CPU 0.01s/0.00u sec elapsed 0.05 sec. INFO: scanned index "Order Items_InsertFlag" to remove 2215 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 0.10 sec. INFO: scanned index "Order Items_ItemFlow2" to remove 2215 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 0.09 sec. INFO: scanned index "Order Items_LastSerCaptureTypeID" to remove 2215 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.09 sec. INFO: scanned index "Order Items_MastInv_FID" to remove 2215 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.11 sec. INFO: scanned index "Order Items_OpCarrFID" to remove 2215 row versions DETAIL: CPU 0.00s/0.03u sec elapsed 0.06 sec. INFO: scanned index "Order Items_OrderDate" to remove 2215 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.06 sec. INFO: scanned index "Order Items_OrderFID" to remove 2215 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 0.01 sec. INFO: scanned index "Order Items_PowerU_FID" to remove 2215 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 0.09 sec. INFO: scanned index "Order Items_Representative" to remove 2215 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.12 sec. INFO: scanned index "Order Items_SelValidUseFID" to remove 2215 row versions DETAIL: CPU 0.03s/0.01u sec elapsed 0.06 sec. INFO: scanned index "Order Items_SerialAuthCode" to remove 2215 row versions DETAIL: CPU 0.00s/0.03u sec elapsed 0.10 sec. INFO: scanned index "Order Items_SerialNum" to remove 2215 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.10 sec. INFO: scanned index "Order Items_SourceID" to remove 2215 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 0.11 sec. INFO: scanned index "Order Items_StatusReasonFID" to remove 2215 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.05 sec. INFO: scanned index "Order Items_SupplyOrderKey" to remove 2215 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 0.11 sec. INFO: scanned index "Order Items_TrailerU_FID" to remove 2215 row versions DETAIL: CPU 0.01s/0.00u sec elapsed 0.09 sec. INFO: scanned index "Order Items_VendorFID" to remove 2215 row versions DETAIL: CPU 0.01s/0.01u sec elapsed 0.08 sec. INFO: "Order Items": removed 2215
Re: Is Autovacuum running?
On Tue, Feb 21, 2023 at 2:56 PM David Rowley wrote: > On Tue, 21 Feb 2023 at 08:42, Brad White wrote: > > > > I'm concerned that Autovacuum may not be running based on the results of > this query. > > > > SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables; > > gives 211 rows like this... > > relname| last_vacuum | last_autovacuum > > BusinessIncidentCategories | null | null > > Valid Use | null | null > > Serial Pool Part Types | null | null > > BusinessIncidentLog| null | null > > Rate Categories| null | null > > It's probably worth having a look at: > > select datname,stats_reset,now() from pg_stat_database where datname = > current_database(); > > while you're connected to the database with those tables. If you're > resetting the stats, then that resets the counters used by autovacuum > and also the columns you mention above. That can lead to autovacuum > not doing any vacuuming or analyzing. > > Well that explains a lot. datname | stats_reset| now DB | 2023-02-17 14:28:27-06 | 2023-02-21 16:16:34-06 I heard that the system was running slowly on Friday. They may have cycled the service in an attempt to resolve that. I went ahead and ran ANALIZE. We'll see if that affects anything. Thanks, Brad.
garbage data back
I was doing a SELECT * FROM view WHERE field LIKE pattern and getting garbage data back. Turns out it is filtering on a different field. As you can see here, when attempting to filter on the FileKey, it is actually filtering on the Order Item ID field. select "FileKey", "OrderItemID" from "vw_rptInvc_Permits" where "FileKey" LIKE '%1317' [image: image.png] I'm stumped. Any guesses?
Fwd: garbage data back
LOL
Joke's on me.
Here's the relevant part of the view
SELECT ...
"Order Items"."ID" AS "OrderItemID",
...
(("Orders"."ID" || '-'::text) || "Order Items"."ID") AS "FileKey"
Wait, then why are the Item IDs different?
Here are the results again, with the FileKey field expanded a bit.
[image: image.png]
On Mon, Mar 6, 2023 at 2:23 PM Adrian Klaver
wrote:
> On 3/6/23 12:17, Brad White wrote:
> > I was doing a SELECT * FROM view WHERE field LIKE pattern
> > and getting garbage data back.
> >
> > Turns out it is filtering on a different field.
> >
> > As you can see here, when attempting to filter on the FileKey, it is
> > actually filtering on the Order Item ID field.
> >
> > select "FileKey", "OrderItemID" from "vw_rptInvc_Permits"
> > where "FileKey" LIKE '%1317'
> >
> > image.png
> > I'm stumped. Any guesses?
>
>
> What is the view definition?
>
> Is there a table named vw_rptInvc_Permits?
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
Re: garbage data back
In that picture, it's clear what is happening.
Here, again, is the result that had me stumped.
[image: image.png]
What threw me was that the field I was looking for had the format of 6d-4d
and this field appeared to have the same format.
But once you expand it, you can see that it has a 6d-7d format and isn't
the field I'm looking for at all. I was trying to take a shortcut by
finding the data in the table instead of digging in and looking up what
field was used in the report. And it appeared at first that I had found
it. ¯\_(?)_/¯
On Mon, Mar 6, 2023 at 2:54 PM David G. Johnston
wrote:
> On Mon, Mar 6, 2023 at 1:48 PM Brad White wrote:
>
>> LOL
>>
>> Joke's on me.
>>
>> Here's the relevant part of the view
>>
>> SELECT ...
>> "Order Items"."ID" AS "OrderItemID",
>> ...
>> (("Orders"."ID" || '-'::text) || "Order Items"."ID") AS "FileKey"
>>
>> Wait, then why are the Item IDs different?
>>
>> Here are the results again, with the FileKey field expanded a bit.
>> [image: image.png]
>>
>
> I don't understand what you are questioning...FileKey is a hyphenated
> concatenation of the ORDER Id and the ORDER ITEM Id, while OrderItemID is
> just the later - and the suffix of the former matches the later.
>
> David J.
>
Re: garbage data back
David, Thanks! 'EXPLAIN ANALYZE' is very slick. Promises to be very helpful. As soon as I saw the output, it was obvious where my problem was. Brad. On Mon, Mar 6, 2023 at 2:30 PM David G. Johnston wrote: > On Mon, Mar 6, 2023 at 1:18 PM Brad White wrote: > >> >> As you can see here, when attempting to filter on the FileKey, it is >> actually filtering on the Order Item ID field. >> >> select "FileKey", "OrderItemID" from "vw_rptInvc_Permits" >> where "FileKey" LIKE '%1317' >> >> I'm stumped. Any guesses? >> > > Agreed, what you've shown doesn't make sense. > > Try: > EXPLAIN ANALYZE > REINDEX (on any underlying table indexes) > > Show the version you are running. > > David J. >
Re: How to fork pg_dump or psql w/o leaking secrets?
Seems to me that your tool could set the env var that you want.
If you don't export it, I think it shouldn't "leak" but your child process
should get it as part of their environment.
On Fri, Sep 22, 2023 at 12:43 PM Dominique Devienne
wrote:
> On Fri, Sep 22, 2023 at 5:19 PM Luca Ferrari wrote:
>
>> On Fri, Sep 22, 2023 at 3:24 PM Dominique Devienne
>> wrote:
>> You added information I was not aware before: you are dumping
>> PostgreSQL to restore it into SQLite, while I was thinking you wanted
>> to do some stuff with a PostgreSQL-to-PostgreSQL backup and restore.
>>
>
> I must be really bad at explaining things today, sorry...
>
> Because of course I'm doing PostgreSQL-to-PostgreSQL backup/restore.
> The fact I use a custom SQLite DB file as the backup's "format" instead of
> a "pile-of-files" (or a TAR of that pile), is the only difference.
>
> That, and the fact it's a backup that mixes one full schema with part of
> another.
> And that my SQLite backup format is much more convenient and "expressive"
> IMHO,
> since fully introspectable ("semi-opaque", since rows are still COPY
> BINARY encoded).
>
> Writing the backup as an SQLite DB incurs a little overhead, of course,
> compared to just appending to per-table files, but not that much when
> in non-transactional mode. Acceptable tradeoff compared to the fact one
> can open the backup easily and see the tables and rows almost as-is,
> which also opens the door to partial restores (there are separates "roots",
> independent entity trees, in these schemas).
>
Start service
I'm trying to start a v15 service on a Windows 2012 R2 server where it hasn't been used for a while. The service is set to run as pgUser. pgUser owns the Postgres directory, including the data dir. The command the service is using is ' "C:\Program Files\PostgreSQL\15\bin\pg_ctl.exe" runservice -N "postgresql-x64-15" -D "C:\Program Files\PostgreSQL\15\data" -w' First off, it's interesting that "runservice" isn't listed as an option for pg_ctl. Windows Event Viewer shows it starting up, handing off logging to pg_log, then timing out. Waiting for server startup... 2023-09-22 12:31:15.173 CDT [5000] LOG: redirecting log output to logging collector process 2023-09-22 12:31:15.173 CDT [5000] HINT: Future log output will appear in directory "pg_log". Timed out waiting for server startup It does create a log file in pg_log, but it is empty. If I sub 'start' for 'runservice' and try to start it from CLI, I get C:\Users\administrator>"C:\Program Files\PostgreSQL\15\bin\pg_ctl.exe" start -N "postgresql-x64-15" -D "C:\Program Files\PostgreSQL\15\data" -w waiting for server to start2023-09-22 12:48:05.438 CDT [4796] FATAL: could not create lock file "postmaster.pid": Permission denied stopped waiting pg_ctl: could not start server Examine the log output. And again, I get an empty log file in pg_log. Is there somewhere else I should check? I get the same results if I pass username and password with -U and -P I'm running as administrator, so not sure how I get permission denied Checking EffectiveAccess shows that admin and pgUser have full control, so I assume this message is a red herring, standing in for the real issue. Without an actual error message to fix, I'm not sure how to proceed. Thanks, Brad.
Re: Start service
Good guess, but no. I should have mentioned that I checked that. There is, though, a postmaster.opts file that wasn't there earlier. It contains C:/Program Files/PostgreSQL/15/bin/postgres.exe "-D" "C:\Program Files\PostgreSQL\15\data" Note the lack of quotes around the exe path. On Fri, Sep 22, 2023 at 1:48 PM Nick Ivanov wrote: > I'd check if there is already "postmaster.pid" in C:\Program > Files\PostgreSQL\15\data, left over from a previous abend. > > On Fri, Sep 22, 2023 at 2:40 PM Brad White wrote: > >> I'm trying to start a v15 service on a Windows 2012 R2 server where it >> hasn't been used for a while. >> The service is set to run as pgUser. >> ... >> If I sub 'start' for 'runservice' and try to start it from CLI, I get >> C:\Users\administrator>"C:\Program >> Files\PostgreSQL\15\bin\pg_ctl.exe" start >> -N "postgresql-x64-15" -D "C:\Program Files\PostgreSQL\15\data" -w >> waiting for server to start2023-09-22 12:48:05.438 CDT [4796] >> FATAL: could >> not create lock file "postmaster.pid": Permission denied >> > > -- > Nick Ivanov > Solution Architect > www.enterprisedb.com >
Re: Start service
I created the pid file by hand and I get C:\Users\administrator>"C:\Program Files\PostgreSQL\15\bin\pg_ctl.exe" start -N "postgresql-x64-15" -D "C:\Program Files\PostgreSQL\15\data" -w -U TMA\pgUse r -P *** pg_ctl: another server might be running; trying to start server anyway waiting for server to start..2023-09-22 14:31:17.752 CDT [4276] FATA L: could not remove old lock file "postmaster.pid": Permission denied 2023-09-22 14:31:17.752 CDT [4276] HINT: The file seems accidentally left over, but it could not be removed. Please remove the file by hand and try again. stopped waiting pg_ctl: could not start server Examine the log output. Remove the file and we are back where we started. But a) we are looking at the right dir, and b) it looks like it might really be a permissions issue somehow. On Fri, Sep 22, 2023 at 1:56 PM Brad White wrote: > Good guess, but no. I should have mentioned that I checked that. > > There is, though, a postmaster.opts file that wasn't there earlier. It > contains > C:/Program Files/PostgreSQL/15/bin/postgres.exe "-D" "C:\Program > Files\PostgreSQL\15\data" > Note the lack of quotes around the exe path. > > On Fri, Sep 22, 2023 at 1:48 PM Nick Ivanov > wrote: > >> I'd check if there is already "postmaster.pid" in C:\Program >> Files\PostgreSQL\15\data, left over from a previous abend. >> >> On Fri, Sep 22, 2023 at 2:40 PM Brad White wrote: >> >>> I'm trying to start a v15 service on a Windows 2012 R2 server where it >>> hasn't been used for a while. >>> The service is set to run as pgUser. >>> ... >>> If I sub 'start' for 'runservice' and try to start it from CLI, I get >>> C:\Users\administrator>"C:\Program >>> Files\PostgreSQL\15\bin\pg_ctl.exe" start >>> -N "postgresql-x64-15" -D "C:\Program Files\PostgreSQL\15\data" -w >>> waiting for server to start2023-09-22 12:48:05.438 CDT [4796] >>> FATAL: could >>> not create lock file "postmaster.pid": Permission denied >>> >> >> -- >> Nick Ivanov >> Solution Architect >> www.enterprisedb.com >> >
Re: Start service
So, there were two issues. Even though admin is in the admin group and had Full Control under effective permissions, somehow that wasn't enough. I noticed that I can run the v14 service, but it had Admin explicitly listed with full control on Data. Once I added that, I started getting error messages when run from CLI and could fix the issue in pg_hba.conf. Now the service starts. Thanks, Brad. On Fri, Sep 22, 2023 at 2:43 PM wrote: > Am 22.09.23 um 20:40 schrieb Brad White: > > I'm trying to start a v15 service on a Windows 2012 R2 server where it > > hasn't been used for a while. > > The service is set to run as pgUser. > > pgUser owns the Postgres directory, including the data dir. > > > > The command the service is using is > > ' "C:\Program Files\PostgreSQL\15\bin\pg_ctl.exe" runservice -N > > "postgresql-x64-15" -D "C:\Program Files\PostgreSQL\15\data" -w' > > > > First off, it's interesting that "runservice" isn't listed as an option > for > > pg_ctl. > > > > Windows Event Viewer shows it starting up, handing off logging to pg_log, > > then timing out. > > Waiting for server startup... > > 2023-09-22 12:31:15.173 CDT [5000] LOG: redirecting log output to > > logging collector process > > 2023-09-22 12:31:15.173 CDT [5000] HINT: Future log output will > > appear in directory "pg_log". > > Timed out waiting for server startup > > > > It does create a log file in pg_log, but it is empty. > > > > If I sub 'start' for 'runservice' and try to start it from CLI, I get > > C:\Users\administrator>"C:\Program > Files\PostgreSQL\15\bin\pg_ctl.exe" > > start > > -N "postgresql-x64-15" -D "C:\Program Files\PostgreSQL\15\data" -w > > waiting for server to start2023-09-22 12:48:05.438 CDT [4796] > > FATAL: could > > not create lock file "postmaster.pid": Permission denied > > stopped waiting > > pg_ctl: could not start server > > Examine the log output. > > > > And again, I get an empty log file in pg_log. > > Is there somewhere else I should check? > > > > I get the same results if I pass username and password with -U and -P > > > > I'm running as administrator, so not sure how I get permission denied > > Checking EffectiveAccess shows that admin and pgUser have full control, > so > > I assume this message is a red herring, standing in for the real issue. > > > > Without an actual error message to fix, I'm not sure how to proceed. > > > > Thanks, > > Brad. > > > if you want to run postgresql server then you could use "pg_ctl start ..." > but to run it as a windows service you first need to register it as a > service. so look at pg_ctl register in the options. > > >
connecting to new instance
I have the v15 service started and listening on 0.0.0.0:5434. Through TCPView, I can see it listening on 5434, I can see the previous version listening and connecting on 5432. I can connect from localhost to port 5434. I have ipv6 turned off in the network settings on both machines. >From any other client or server, I get "Are you sure the server is running on 192.168.1.112 and accepting connections on 5434? The relevant line in pg_hba.conf looks like: hostall all 192.168.1.0/24 password I can't see what else I'm missing.
Move from v9.4 to v15
I wanted to thank everyone who helped out in our transition to the new version. Thanks especially to Tom, Laurenz, and Adrian. It has taken over a year, since this upgrade project kept getting superseded by more important projects, and because I only work with this client one day a week, but as of this morning, we are finally operating on v15. The big issue we ran into, and solved months ago, was the change in default date time precision. Besides being on a current version, the two big benefits we are looking forward to are 1. support for tracking long running queries. 2. improved support for replication enabling us to set up a hot backup. On the flip side, expect more questions as we attempt to get replication set up. 8: -) Thanks, Brad.
setting up streaming replication
I'm stumped. Using this command to set up the slave and replication on PG v 15: "C:\Program Files\PostgreSQL\15\bin\pg_basebackup" -h 192.168.1.126 -U pgrep_user -p 5433 -D "C:\Program Files\PostgreSQL\15\data" -Fp -Xs -R If I have PG running on the remote server and the data directory is intact, then I get an error, Data exists and is not empty. If I shut down PG, delete data\*, restart PG, then it fails to start because the conf files are missing. If I leave PG shut down and run pg_basebackup, it times out and asks if pg is running and listening on the port. Of course it is not. I see lots of posts on how to use pg_basebackup, but apparently this isn't interesting because no one mentions whether pg should be running on the remote server. Just that the data dir must be empty. I know this works because I've done it before. But I don't see the trick I'm missing. Thanks, Brad.
Re: setting up streaming replication
On Tue, Oct 24, 2023 at 8:56 AM Ron wrote: > On 10/24/23 08:14, b55white wrote: > > Is all that stuff with slots necessary since the backup will automatically > create a temporary slot for replication? > > > I want a permanent slot so that replication automatically resumes if the > secondary must temporarily be taken down. > > -- > Wait. Are you saying that once I get streaming replication set up, it quits working when I reboot the servers once a week? Thanks, Brad.
setting up streaming replication, part 2
I have the replication server set up and the streaming is working.
The latest data show up, as desired.
3 minor issues.
1) I also have the WAL files being copied to a common location from the
primary server with
archive_command = 'copy %p
"DISKSTATION\\AccessData\\Prod\\WALfiles\\%f"'
It's not clear to me how the replication server finds those files.
I also have the cleanup set to go, but it doesn't seem to be cleaning up
any of the archived files.
archive_cleanup_command = 'pg_archivecleanup
DISKSTATION\\AccessData\\Prod\\WALfiles %r'
2) I have the connection string set for the replication server to connect,
primary_conninfo = 'host = 192.168.1.112 port = 5433 user =
{pg_rep_user }password = {password}'
But the log file shows
LOG: waiting for WAL to become available at 2/A1B8
FATAL: could not connect to the primary server: connection to server
at "{IP}", port 5433 failed: fe_sendauth: no password supplied
3) When I run
"C:\Program Files\PostgreSQL\15\bin\pg_ctl.exe" promote -D "C:\Program
Files\PostgreSQL\15\data"
on the backup to promote it to Primary, I get
pg_ctl: could not send promote signal (PID: 3996): Operation not
permitted
Thanks for all the help so far,
Brad.
Re: setting up streaming replication, part 2
On Tue, Oct 24, 2023, 9:02 PM Ron wrote:
> On 10/24/23 19:29, Brad White wrote:
> > I have the replication server set up and the streaming is working.
> > The latest data show up, as desired.
> >
> > 3 minor issues.
> >
>
> > 2) I have the connection string set for the replication server to
> connect,
> > primary_conninfo = 'host = 192.168.1.112 port = 5433 user =
> > {pg_rep_user }password = {password}'
> > But the log file shows
> > LOG: waiting for WAL to become available at 2/A1B8
> > FATAL: could not connect to the primary server: connection to
> server
> > at "{IP}", port 5433 failed: fe_sendauth: no password supplied
>
> But you said that streaming is working.
>
> Yes, I realized after I posted that this would be confusing.
I cheated and changed hba from password to trust until I get this resolved.
Not a long term solution.
Having replication working was more important in the short term.
>
Re: setting up streaming replication, part 2
From: Ron
Sent: Wednesday, October 25, 2023 4:35:59 AM
To: pgsql-generallists.postgresql.org
Subject: Re: setting up streaming replication, part 2
On 10/24/23 23:47, Brad White wrote:
On Tue, Oct 24, 2023, 9:02 PM Ron
mailto:ronljohnso...@gmail.com>> wrote:
On 10/24/23 19:29, Brad White wrote:
> I have the replication server set up and the streaming is working.
> The latest data show up, as desired.
>
> 3 minor issues.
>
> 2) I have the connection string set for the replication server to connect,
> primary_conninfo = 'host = 192.168.1.112 port = 5433 user =
> {pg_rep_user }password = {password}'
> But the log file shows
> LOG: waiting for WAL to become available at 2/A1B8
> FATAL: could not connect to the primary server: connection to server
> at "{IP}", port 5433 failed: fe_sendauth: no password supplied
But you said that streaming is working.
Yes, I realized after I posted that this would be confusing.
I cheated and changed hba from password to trust until I get this resolved. Not
a long term solution.
Having replication working was more important in the short term.
Follow the tecmint link in my original email.
https://www.postgresql.org/message-id/7a9570a6-07a9-4b41-b419-b4a0aa67fc53%40gmail.com
It worked perfectly for me.
Looks like they have double single quotes around the password.
Re: pg_dump/pg_restore --jobs practical limit?
From: Ron Sent: Thursday, November 2, 2023 3:01:47 AM To: pgsql-general@lists.postgresql.org Subject: Re: pg_dump/pg_restore --jobs practical limit? On 11/1/23 15:42, Laurenz Albe wrote: On Wed, 2023-11-01 at 13:09 -0500, Ron wrote: I will "soon" migrate some 1+ TB database from Pg 9.6.24 on RHEL 6 VMs to Pg 14.latest on RHEL 8 VMs. The VMs have 10Gbps "NICs", SAN-based LUNs managed by LVM, and are all on ESX blades. nproc count on some is 16 and on others is 32. Does anyone have experience as to the point of diminishing returns? IOW, can I crank them processes up to --jobs=30, will I see no gain -- or even degradation -- after, for example, --jobs=24? This would be for both pg_dump and pg_restore (which would be run on the RHEL 8 VM). Test, test, test. Theoretical considerations are pretty worthless, Which is why I asked if anyone has experience. and it is easy to measure that. Not necessarily. Our test systems are way too small (only good enough to validate that the script works correctly), and there's always something (sometimes a lot, sometime just "some") going on in production, whether it's my customer's work, or the SAN (like snapshotting every VM and then copying the snapshots to the virtual tape device) or something else. -- Sure, but are the new systems busy already? Ideally you could run tests on them before they are put into production.
replication not replicating
Errors from the primary 2024-01-15 00:00:51.157 CST [2660] ERROR: requested WAL segment 0001000200A2 has already been removed 2024-01-15 00:00:51.157 CST [2660] STATEMENT: START_REPLICATION 2/A200 TIMELINE 1 2024-01-15 00:00:56.158 CST [492] ERROR: requested WAL segment 0001000200A2 has already been removed 2024-01-15 00:00:56.158 CST [492] STATEMENT: START_REPLICATION 2/A200 TIMELINE 1 2024-01-15 00:00:56.159 CST [492] LOG: could not receive data from client: An existing connection was forcibly closed by the remote host. 2024-01-15 00:01:01.151 CST [3404] ERROR: requested WAL segment 0001000200A2 has already been removed 2024-01-15 00:01:01.151 CST [3404] STATEMENT: START_REPLICATION 2/A200 TIMELINE 1 2024-01-15 00:01:01.152 CST [3404] LOG: could not receive data from client: An existing connection was forcibly closed by the remote host. [repeat for 55000 lines] Errors from the hot backup at roughly the same time 2024-01-15 01:13:57.893 CST [2988] LOG: started streaming WAL from primary at 2/A200 on timeline 1 2024-01-15 01:13:57.893 CST [2988] FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 0001000200A2 has already been removed 2024-01-15 01:13:57.893 CST [1792] LOG: waiting for WAL to become available at 2/A2002000 2024-01-15 01:14:02.884 CST [2552] LOG: started streaming WAL from primary at 2/A200 on timeline 1 2024-01-15 01:14:02.884 CST [2552] FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 0001000200A2 has already been removed 2024-01-15 01:14:02.884 CST [1792] LOG: waiting for WAL to become available at 2/A2002000 [repeat for 49000 lines]
replication isn't replicating
Errors from the Primary server 2024-01-15 00:01:06.166 CST [1428] ERROR: requested WAL segment 0001000200A2 has already been removed 2024-01-15 00:01:06.166 CST [1428] STATEMENT: START_REPLICATION 2/A200 TIMELINE 1 2024-01-15 00:01:11.158 CST [3472] ERROR: requested WAL segment 0001000200A2 has already been removed 2024-01-15 00:01:11.158 CST [3472] STATEMENT: START_REPLICATION 2/A200 TIMELINE 1 2024-01-15 00:01:11.158 CST [3472] LOG: could not receive data from client: An existing connection was forcibly closed by the remote host. 2024-01-15 00:01:16.166 CST [664] ERROR: requested WAL segment 0001000200A2 has already been removed 2024-01-15 00:01:16.166 CST [664] STATEMENT: START_REPLICATION 2/A200 TIMELINE 1 2024-01-15 00:01:21.161 CST [2016] ERROR: requested WAL segment 0001000200A2 has already been removed 2024-01-15 00:01:21.161 CST [2016] STATEMENT: START_REPLICATION 2/A200 TIMELINE 1 2024-01-15 00:01:21.161 CST [2016] LOG: could not receive data from client: An existing connection was forcibly closed by the remote host. [repeat for 55 lines] Errors from the backup server 2024-01-15 01:13:57.893 CST [2988] LOG: started streaming WAL from primary at 2/A200 on timeline 1 2024-01-15 01:13:57.893 CST [2988] FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 0001000200A2 has already been removed 2024-01-15 01:13:57.893 CST [1792] LOG: waiting for WAL to become available at 2/A2002000 2024-01-15 01:14:02.884 CST [2552] LOG: started streaming WAL from primary at 2/A200 on timeline 1 2024-01-15 01:14:02.884 CST [2552] FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 0001000200A2 has already been removed 2024-01-15 01:14:02.884 CST [1792] LOG: waiting for WAL to become available at 2/A2002000 [repeat for 49000 lines] What's my next step? Thanks, Brad.
Re: replication isn't replicating
Sorry for the repeat. It looked like it hadn't been sent. 😔 >
Re: replication not replicating
On Tue, Jan 16, 2024 at 4:35 PM Ron Johnson wrote: > On Tue, Jan 16, 2024 at 4:10 PM Brad White wrote: > >> Errors from the primary >> >> 2024-01-15 00:00:51.157 CST [2660] ERROR: requested WAL segment >> 0001000200A2 has already been removed >> 2024-01-15 00:00:51.157 CST [2660] STATEMENT: START_REPLICATION >> 2/A200 TIMELINE 1 >> 2024-01-15 00:00:56.158 CST [492] ERROR: requested WAL segment >> 0001000200A2 has already been removed >> 2024-01-15 00:00:56.158 CST [492] STATEMENT: START_REPLICATION >> 2/A200 TIMELINE 1 >> 2024-01-15 00:00:56.159 CST [492] LOG: could not receive data from >> client: An existing connection was forcibly closed by the remote host. >> >> >> 2024-01-15 00:01:01.151 CST [3404] ERROR: requested WAL segment >> 0001000200A2 has already been removed >> 2024-01-15 00:01:01.151 CST [3404] STATEMENT: START_REPLICATION >> 2/A200 TIMELINE 1 >> 2024-01-15 00:01:01.152 CST [3404] LOG: could not receive data from >> client: An existing connection was forcibly closed by the remote host. >> >> [repeat for 55000 lines] >> >> > What database version? > v15 > Streaming or Logical replication? > IDK > Using a slot? > No. Relevant sections from the conf --- Primary --- #-- # WRITE-AHEAD LOG #-- # - Settings - wal_level = replica # minimal, replica, or logical # - Archiving - archive_mode = on # enables archiving; off, on, or always # (change requires restart) #archive_library = '' # library to use to archive a logfile segment # (empty string indicates archive_command should # be used) archive_command = 'copy %p "DISKSTATION\\AccessData\\Prod\\WALfiles\\%f"' # command to use to archive a logfile segment archive_timeout = 0# force a logfile segment switch after this # number of seconds; 0 disables primary_conninfo is set. Everything else is commented out, using default values. --- Backup Server --- #-- # WRITE-AHEAD LOG #-- # - Settings - wal_level = replica# minimal, replica, or logical archive_cleanup_command = 'pg_archivecleanup DISKSTATION\\AccessData\\Prod\\WALfiles %r' # - Archiving - archive_mode = on # - Archive Recovery - #restore_command = '' >
Re: replication isn't replicating
On Tue, Jan 16, 2024 at 3:53 PM Emanuel Calvo <3man...@gmail.com> wrote: > > El mar, 16 ene 2024 a las 22:47, Brad White () > escribió: > >> Errors from the Primary server >> >> 2024-01-15 00:01:06.166 CST [1428] ERROR: requested WAL segment >> 0001000200A2 has already been removed >> 2024-01-15 00:01:06.166 CST [1428] STATEMENT: START_REPLICATION >> 2/A200 TIMELINE 1 >> 2024-01-15 00:01:11.158 CST [3472] ERROR: requested WAL segment >> 0001000200A2 has already been removed >> 2024-01-15 00:01:11.158 CST [3472] STATEMENT: START_REPLICATION >> 2/A200 TIMELINE 1 >> 2024-01-15 00:01:11.158 CST [3472] LOG: could not receive data from >> client: An existing connection was forcibly closed by the remote host. >> >> > These log entries mean that some node is requesting a WAL segment that was > already removed from > the server. > > 2024-01-15 01:14:02.884 CST [2552] LOG: started streaming WAL from >> primary at 2/A200 on timeline 1 >> 2024-01-15 01:14:02.884 CST [2552] FATAL: could not receive data from >> WAL stream: ERROR: requested WAL segment 0001000200A2 has >> already been removed >> 2024-01-15 01:14:02.884 CST [1792] LOG: waiting for WAL to become >> available at 2/A2002000 >> >> > > These are related to the backup not finding that segment, so it means > you'll need to resync > your backup stream. > You pointed me in the right direction. Turns out the files are still there, so it must be a permission issue. pgUser has full access to the files. Postgres is running as pgUser, except that wasn't true on the backup. Was running as 'Network Service'. Should be better now. Aaaand I'm wrong. Still getting the same errors on both servers.
Re: replication not replicating
On Tue, Jan 16, 2024 at 6:24 PM Ron Johnson wrote: > On Tue, Jan 16, 2024 at 6:26 PM Brad White wrote: > >> On Tue, Jan 16, 2024 at 4:35 PM Ron Johnson >> wrote: >> >>> >>>> What database version? >>> >> v15 >> >> >>> Streaming or Logical replication? >>> >> IDK >> >>> Using a slot? >>> >> No. >> >> Relevant sections from the conf >> >> --- Primary --- >> >> #-- >> # WRITE-AHEAD LOG >> >> #-- >> >> # - Settings - >> >> wal_level = replica # minimal, replica, or logical >> >> # - Archiving - >> >> archive_mode = on # enables archiving; off, on, or always >> # (change requires restart) >> #archive_library = '' # library to use to archive a logfile >> segment >> # (empty string indicates archive_command should >> # be used) >> archive_command = 'copy %p >> "DISKSTATION\\AccessData\\Prod\\WALfiles\\%f"' >> # command to use to archive a >> logfile segment >> archive_timeout = 0# force a logfile segment switch after this >># number of seconds; 0 disables >> >> primary_conninfo is set. >> Everything else is commented out, using default values. >> >> --- Backup Server --- >> >> #-- >> # WRITE-AHEAD LOG >> >> #-- >> >> # - Settings - >> >> wal_level = replica# minimal, replica, or logical >> archive_cleanup_command = 'pg_archivecleanup >> DISKSTATION\\AccessData\\Prod\\WALfiles %r' >> >> # - Archiving - >> >> archive_mode = on >> >> # - Archive Recovery - >> #restore_command = '' >> > > Hmmm. Are you log shipping? > > If so, why? Streaming Replication is sooo much easier. > I'm attempting async streaming.
Re: replication not replicating
On Tue, Jan 16, 2024 at 7:33 PM Brad White wrote: > On Tue, Jan 16, 2024 at 6:24 PM Ron Johnson > wrote: > >> On Tue, Jan 16, 2024 at 6:26 PM Brad White wrote: >> >>> On Tue, Jan 16, 2024 at 4:35 PM Ron Johnson >>> wrote: >>> >>>> >>>>> What database version? >>>> >>> v15 >>> >>> >>>> Streaming or Logical replication? >>>> >>> IDK >>> >>>> Using a slot? >>>> >>> No. >>> >>> Relevant sections from the conf >>> >>> archive_mode = on >>> >>> # - Archive Recovery - >>> #restore_command = '' >>> >> >> Hmmm. Are you log shipping? >> >> If so, why? Streaming Replication is sooo much easier. >> > > I'm attempting async streaming. > Looks like I was just missing the restore command. Everything is looking happier now.
WAL file clean up
I have the 'archive_cleanup_command' command specified, but I still have WAL files. The documentation seems to indicate that it will run automatically, but it doesn't seem to be running. archive_cleanup_command = 'pg_archivecleanup DISKSTATION\\AccessData\\Prod\\WALfiles %r' # command to execute at every restartpoint It seems it should run every time there is a restartpoint. Restartpoints can happen at any checkpoint in the log. My checkpoint time out is set to 5 minutes. checkpoint_timeout = 5min Restartpoints are more likely to happen when getting closer to the size limit. max_wal_size = 1GB My folder size is now 430 files = 6.8 GB. Not terrible, but should be enough to trigger a restartpoint. How do I tell if I haven't had a restartpoint or I did and the command didn't work. No errors in the pg_log Thanks, Brad.
