pg_restore enhancements
After working for a site where we are constantly doing logical pg_dump to refresh environments I've come to miss features available in other RDBMS' refresh/restore utilities. Someone could point me in the right direction otherwise, but pg_restore seems to be lacking the ability to resume a restore upon failure, is all or nothing with this guy. There also doesn't seem to be a way to control batch size when doing the COPY phase, therefore preventing the WAL directory from filling up and crashing the system. IMHO, it would be nice to have a feature that would allow pg_restore to resume based on which part of the restore and/or object have already been restored. When it comes to the COPY phase of the restore, it would be nice to be able to control batch size and resume COPY of a particular object upon failure. Thanks in advance for any suggestions or the green light to post this to the PG-developer group :-) Thanks,Efrain J. Berdecia
Re: pg_restore enhancements
Thanks, I'm trying to gage the interest on such a feature enhancement. Up to now I have not actively contributed to the Postgres Project but this is itching my rusty programming fingers lol Thanks,Efrain J. Berdecia On Wednesday, November 22, 2023 at 08:28:18 AM EST, David G. Johnston wrote: On Wednesday, November 22, 2023, Efrain J. Berdecia wrote: Thanks in advance for any suggestions or the green light to post this to the PG-developer group :-) If you aren’t offering up a patch for these it isn’t developer material and belongs right here. David J.
Re: pg_restore enhancements
Thanks, the issue we've run into, which I guess could be really a setup issue, with running a COPY command while executing pg_restore, is that if we are restoring a large table (bigger than 500GB) our WAL directory can grow to be very large. I would think that if the pg_restore or COPY command was able to support a batch-size option, this should allow postgres to either archive or remove wal files and prevent having to re-size the WAL directory for a one time refresh operation. I'm trying to gage how feasible would be to start looking at contributing to add such a feature to either the COPY command or pg_restore. Thanks,Efrain J. Berdecia On Wednesday, November 22, 2023 at 11:37:13 AM EST, Adrian Klaver wrote: On 11/22/23 05:25, Efrain J. Berdecia wrote: > After working for a site where we are constantly doing logical pg_dump > to refresh environments I've come to miss features available in other > RDBMS' refresh/restore utilities. > > Someone could point me in the right direction otherwise, but pg_restore > seems to be lacking the ability to resume a restore upon failure, is all > or nothing with this guy. There also doesn't seem to be a way to control > batch size when doing the COPY phase, therefore preventing the WAL > directory from filling up and crashing the system. The above needs more information on Postgres version(community or fork), OS and version, the size of the data set, the storage type and size, the Postgres conf, etc. Restores are being done all the time and this is the first report, as far as I can remember, about an issue with COPY and WAL in a restore. pg_restore https://www.postgresql.org/docs/current/app-pgrestore.html Does have: --section=sectionname Only restore the named section. The section name can be pre-data, data, or post-data. This option can be specified more than once to select multiple sections. The default is to restore all sections. The data section contains actual table data as well as large-object definitions. Post-data items consist of definitions of indexes, triggers, rules and constraints other than validated check constraints. Pre-data items consist of all other data definition items. AND -l --list List the table of contents of the archive. The output of this operation can be used as input to the -L option. Note that if filtering switches such as -n or -t are used with -l, they will restrict the items listed. -L list-file --use-list=list-file Restore only those archive elements that are listed in list-file, and restore them in the order they appear in the file. Note that if filtering switches such as -n or -t are used with -L, they will further restrict the items restored. list-file is normally created by editing the output of a previous -l operation. Lines can be moved or removed, and can also be commented out by placing a semicolon (;) at the start of the line. See below for examples. > > IMHO, it would be nice to have a feature that would allow pg_restore to > resume based on which part of the restore and/or object have already > been restored. > > When it comes to the COPY phase of the restore, it would be nice to be > able to control batch size and resume COPY of a particular object upon > failure. COPY as it stands now is all or none, so that command would have to be changed. > > Thanks in advance for any suggestions or the green light to post this to > the PG-developer group :-) > > Thanks, > Efrain J. Berdecia -- Adrian Klaver adrian.kla...@aklaver.com
Re: Question on indexes
They are extremely efficient for joins!!! Yahoo Mail: Search, Organize, Conquer On Thu, Oct 10, 2024 at 2:52 PM, Christophe Pettus wrote: > On Oct 10, 2024, at 11:49, sud wrote: > > Hi, > I have never used any 'hash index' but saw documents in the past suggesting > issues around hash index , like WAL doesnt generate for "hash index" which > means we can't get the hash index back after crash also they are not applied > to replicas etc. That's very old information. Hash indexes are correctly WAL-logged since (IIRC) version 10.
Re: Suddenly all queries moved to seq scan
Make sure to run analyze on the entire database, possibly using vacuumdb would be faster. Also, check for invalid indexes. Efrain J. Berdecia On Wednesday, November 20, 2024 at 08:02:36 AM EST, Daniel Gustafsson wrote: > On 20 Nov 2024, at 11:50, Sreejith P wrote: > We are using PostgresQL 10 in our production database. We have around 890 > req /s request on peak time. PostgreSQL 10 is well out of support and does not receive bugfixes or security fixes, you should plan a migration to a supported version sooner rather than later. > 2 days back we applied some patches in the primary server and restarted. We > didn't do anything on the secondary server. Patches to the operating system, postgres, another application? > Next day, After 18 hours all our queries from secondary servers started > taking too much time. queries were working in 2 sec started taking 80 > seconds. Almost all queries behaved the same way. > > After half an hour of outage we restarted all db servers and system back to > normal. > > Still we are not able to understand the root case. We couldn't find any error > log or fatal errors. During the incident, in one of the read server disks > was full. We couldn't see any replication lag or query cancellation due to > replication. You say that all queries started doing sequential scans, is that an assumption from queries being slow or did you capture plans for the queries which be compared against "normal" production plans? -- Daniel Gustafsson
Re: Bash function from psql (v14)
Maybe try using full path...
Yahoo Mail: Search, Organize, Conquer
On Sat, Nov 9, 2024 at 12:41 PM, Murthy Nunna wrote:
Hi,
I am trying to code bash function and call it from psql. But it is failing. How
can I get this to work. Creating a separate script instead of a function works,
but I do not want to do that. I have too many variables to be passed back and
forth. Any ideas?
#!/bin/bash
psql -d postgres -p 5433 <<-PSQLBLOCK
\! run-bash-function -- This doesn’t work
PSQLBLOCK
# Run the function outside PSQLBLOCK. This works!
run-bash-function
exit $?
# Create bash function
run-bash-function ()
{
echo "in bash function"
}
# end of bash script
Run the above script:
./test-bash-function.sh
sh: line 1: run-bash-function: command not found
in bash function
Re: Different execution plans in PG17 and pgBouncer...
Is the query using parameter markers? Is the source executing the query forcing a "bad" data type casting? Yahoo Mail: Search, Organize, Conquer On Mon, May 5, 2025 at 8:52 AM, Mladen Marinović wrote: On Mon, May 5, 2025 at 2:38 PM SERHAD ERDEM wrote: Hi , you had better try vacuum analyze for the whole db , pgbouncer connection layer can not causeslow queries. I did that already. But the slow query is the consequence of the different plan, not the statistics. From: Mladen Marinović Sent: Monday, May 5, 2025 12:27 PM To: Achilleas Mantzios Cc: pgsql-general@lists.postgresql.org Subject: Re: Different execution plans in PG17 and pgBouncer... On Mon, May 5, 2025 at 12:07 PM Achilleas Mantzios wrote: On 5/5/25 11:00, Mladen Marinović wrote: On Mon, May 5, 2025 at 11:24 AM Achilleas Mantzios wrote: On 5/5/25 09:52, Mladen Marinović wrote: Hi, We recently migrated our production instances from PG11 to PG17. While doing so we upgraded our pgBouncer instances from 1.12 to 1.24. As everything worked on the test servers we pushed this to production a few weeks ago. We did not notice any problems until a few days ago (but the problems were here from the start). The main manifestation of the problems is a service that runs a fixed query to get a backlog of unprocessed data (limited to a 1000 rows). When testing the query using pgAdmin connected directly to the database we get a result in cca. 20 seconds. The same query runs for 2 hours when using pgBouncer to connect to the same database. That's a huge jump, I hope you guys did extensive testing of your app. In which language is your app written? If java, then define prepareThreshold=0 in your jdbc and setmax_prepared_statements = 0in pgbouncer. Mainly python, but the problem was noticed in a java service.Prepare treshold was already set to 0. We changed the max_prepared_statementsto 0 from the default (200) but no change was noticed. How about search paths ? any difference on those between the two runs ? Do you set search_path in pgbouncer ? what is "cca." btw ? The more interesting part is that when we issue an explain of the same query we get different plans. We did this a few seconds apart so there should be no difference in collected statistics. We ruled out prepared statements, as we suspected the generic plan might be the problem, but it is not. Is there any pgBouncer or PG17 parameter that might be the cause of this? Does this spawn any connections (such as dblink) ? are there limits per user/db pool_size in pgbouncer ? No additional connection nor dbling. Just plain SQL (CTE, SELECT, INSERT, UPDATE, DELETE,...) There are limits, but they are not hit. The query just uses a different plan and runs slower because of that. Pgbouncer, in contrast to its old friend PgPool-II is completely passive, just passes through SQL to the server as fast as possible as it can. But I am sure you know that. Good luck, keep us posted! Yes, that is what puzzles me. What is the pgbouncer's timeout in the server connections ? How about "idle in transaction" ? do you get any of those? What's the isolation level ? How about the user ? is this the same user doing pgadmin queries VS via the app ? Can you identify the user under which the problem is manifested and : ALTER user "unlucky_user" SET log_statement = 'all'; ALTER user "unlucky_user" SET log_min_duration_statement = 0; -- to help you debug the prepared statements .. just in case , and other stuff not printed by log_statement = all. None of those parameters should affect the fact that when issuing the explain select query (the statement is not prepared) from psql directly gives a different result than issuing it over the pgbouncer connection. The result is repeatable. We have rolled back pgbouncer to 1.12. and it seems the problem persists. This is one of the weirdest things I have ever seen with PostgreSQL. Regards,Mladen Marinović
