Re: Explain returns different number of rows
On 2022-10-20 09:56:23 -0700, Christophe Pettus wrote: > On Oct 20, 2022, at 09:52, Vince McMahon > wrote: > > The number of rows are different. > > This isn't unexpected. EXPLAIN does not actually run the query and > determine how many rows are returned; it calculates an estimate based > on the current system statistics, which vary constantly depending on > activity in the database. EXPLAIN ANALYZE (which is what he did) does run the query and return the actual number of rows: #v+ wdsah=> explain (analyze, buffers) select * from facttable_eurostat_comext_cpa2_1 ; ╔══╗ ║QUERY PLAN ║ ╟──╢ ║ Seq Scan on facttable_eurostat_comext_cpa2_1 (cost=0.00..1005741.32 rows=39633432 width=85) (actual time=0.396..6541.701 rows=39633591 loops=1) ║ ║ Buffers: shared read=609407 ║ ║ Planning Time: 1.650 ms ║ ║ Execution Time: 7913.027 ms ║ ╚══╝ (4 rows) #v- The first tuple (cost=0.00..1005741.32 rows=39633432 width=85) is an estimate used to plan the query. But the second one (actual time=0.396..6541.701 rows=39633591 loops=1) contains measurements from actually running the query. I think it's possible that the rows estimate in the first tuple changes without any actual data change (although the only reason I can think of right now would be an ANALYZE (in another session or by autovacuum)). But the actual rows definitely shouldn't change. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
synchronous streaming replication
Hi, I have a question. Its stays at the intersection of software engineering and PostgreSQL. I have configured streaming synchronous replication and whit setting "synchronous_commit=remote_apply" to make sure that the slave will always respond the same as the MASTER (this is a developers' requirement that the MASTER always responds the same as SLAVE). I set "hot_standby_feedback=on" and "max_standby_streaming_delay=-1", max_standby_streaming_delay set to -1 to make MASTER wait indefinitely before SELECT conflicts on the SLAVE will end. Here's where the problem arises, because not long after the replication has been started some serious delays occur in the form of "replay_lag" - which rather indicates the appearance of conflicts; the replication stops working properly. >From the server logs it appears that UPDATE (select for update) has occurred on the MASTER, and SELECT queries are in progress on SLAVE causing replication conflicts, with setting "max_standby_streaming_delay=-1" they never ends and there are huge lags. >From the findings with the developers it emerged that they do not want me to set max_standby_streaming_delay to a value after which the queries conflicted with replication will be canceled. - So I'm wondering if, in this configuration, it can work properly at all without setting, for example, "max_standby_streaming_delay=30" ? - On the other hand I wonder if the application should not be developed in such a way to support replication of PostgreSQL configured as a streaming synchronous replication cluster with "synchronous_commit=remote_apply" ? - Or perhaps "synchronous streaming replication" is a bad choice, maybe logical replication would be better ? - What are the best practices? - Perhaps you just need to force/teach applications to work with synchronous replication in such a way that when the SELECT causes conflicts with replication such queries are canceled and the application should resend/repeat query ? - I also think that after setting, for example, "max_standby_streaming_delay=30" queries (addressed) to the database should be very well optimized, so that too long queries are not canceled too frequently? - Do you know any books focused on applications adapted to work in postgresql synchronous streaming replication environment i.e. High Availability? Joseph
Re: High CPU usage
On 10/20/22 12:59, ertan.kucuko...@1nar.com.tr wrote:
Hello,
I am using PostgreSQL v14.5 on Linux Debian 11.5. I recently observe very
high CPU usage on my Linux system as below
PID USER PR NIVIRTRESSHR S %CPU %MEM TIME+
COMMAND
2357756 postgres 20 0 2441032 2,3g 4 S 298,7 67,9 2114:58
Tspjzj2Z
The USER is the system user postgres which could be running any program
not necessarily the Postgres server. In this case that would seem to be
the program Tspjzj2Z.
Try running:
top -U postgres -c
to get the full command line under COMMAND.
I could not find any file named Tspjzj2Z on the file system. I could not
find PID number using below SQL
I doubt this has anything to do with the Postgres server itself.
Is this machine accessible to the Internet?
There is the possibility you have had an intrusion.
SELECT * FROM pg_stat_activity
I also see another suggestion like below to identify long running queries
SELECT max(now() - xact_start) FROM pg_stat_activity
WHERE state IN ('idle in transaction',
'active');
I get no long running query at all.
There is no replication of any kind. This is a single instance server which
alows certification login only.
I appreciate any help to figure this out.
Thanks & Regards,
Ertan
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: pg_restore 12 "permission denied for schema" errors
On 10/20/22 14:34, Ron wrote: On 10/20/22 10:02, Adrian Klaver wrote: On 10/20/22 06:20, Ron wrote: On 10/20/22 00:12, Tom Lane wrote: I ran "pg_dumpall --globals-only --no-role-passwords" on the source instance, and applied it to the new instance before doing the pg_restore. If I hadn't done that, pg_restore would have thrown errors on all the GRANT and ALTER TABLE ... OWNER TO statements embedded in the backup. Some questions: 1) The backup was from a Postgres 12.x database using a version 12 or higher instance of pg_backup? pg_dump and pg_restore are 12.11 from RHEL8. 3) What if you run without --jobs? It runs without error. Add "--jobs=2" and the errors appear. Hmm, that is beyond me. 1) I did notice that the pg_restore errors all where; ERROR: permission denied for schema strans 2) They all occurred during CREATE INDEX or COPY, which would be the part where --jobs kicks in. To me it looks like out of order execution where the jobs starting on their tasks before the main task got done granting permissions. I just have no idea how that could happen. -- Adrian Klaver adrian.kla...@aklaver.com
Re: pg_restore 12 "permission denied for schema" errors
On 10/22/22 11:20, Adrian Klaver wrote: On 10/20/22 14:34, Ron wrote: On 10/20/22 10:02, Adrian Klaver wrote: On 10/20/22 06:20, Ron wrote: On 10/20/22 00:12, Tom Lane wrote: I ran "pg_dumpall --globals-only --no-role-passwords" on the source instance, and applied it to the new instance before doing the pg_restore. If I hadn't done that, pg_restore would have thrown errors on all the GRANT and ALTER TABLE ... OWNER TO statements embedded in the backup. Some questions: 1) The backup was from a Postgres 12.x database using a version 12 or higher instance of pg_backup? pg_dump and pg_restore are 12.11 from RHEL8. 3) What if you run without --jobs? It runs without error. Add "--jobs=2" and the errors appear. Hmm, that is beyond me. 1) I did notice that the pg_restore errors all where; ERROR: permission denied for schema strans 2) They all occurred during CREATE INDEX or COPY, which would be the part where --jobs kicks in. To me it looks like out of order execution where the jobs starting on their tasks before the main task got done granting permissions. I just have no idea how that could happen. I was afraid you were going to say that. The work-around is to: pg_dump $SRCDB --schema-only | grep -e '^\(GRANT|REVOKE\)' > all_GRANT.sql pg_dump $SRCDB --schema-only | grep OWNER > all_OWNER.sql pg_restore --jobs=X --no-owner $NEWDB psql $NEWDB -f all_OWNER.sql psql $NEWDB -f all_GRANT.sql This is, of course, why we need to test the backup/restore process. -- Angular momentum makes the world go 'round.
Re: pg_restore 12 "permission denied for schema" errors
On 10/22/22 09:41, Ron wrote: On 10/22/22 11:20, Adrian Klaver wrote: On 10/20/22 14:34, Ron wrote: On 10/20/22 10:02, Adrian Klaver wrote: On 10/20/22 06:20, Ron wrote: On 10/20/22 00:12, Tom Lane wrote: I was afraid you were going to say that. The work-around is to: pg_dump $SRCDB --schema-only | grep -e '^\(GRANT|REVOKE\)' > all_GRANT.sql pg_dump $SRCDB --schema-only | grep OWNER > all_OWNER.sql pg_restore --jobs=X --no-owner $NEWDB The above and below have me confused. What is $NEWDB? In above it seems to be a file and below a database name. psql $NEWDB -f all_OWNER.sql psql $NEWDB -f all_GRANT.sql This is, of course, why we need to test the backup/restore process. -- Adrian Klaver adrian.kla...@aklaver.com
Re: High CPU usage
ertan.kucuko...@1nar.com.tr schrieb am 20.10.2022 um 21:59: Hello, I am using PostgreSQL v14.5 on Linux Debian 11.5. I recently observe very high CPU usage on my Linux system as below PID USER PR NIVIRTRESSHR S %CPU %MEM TIME+ COMMAND 2357756 postgres 20 0 2441032 2,3g 4 S 298,7 67,9 2114:58 Tspjzj2Z The program name looks suspiciously as if you have been hacked. See e.g. https://stackoverflow.com/questions/50149034 https://dba.stackexchange.com/questions/191282/ https://www.imperva.com/blog/deep-dive-database-attacks-scarlett-johanssons-picture-used-for-crypto-mining-on-postgre-database/
Re: pg_restore 12 "permission denied for schema" errors
On 10/22/22 12:00, Adrian Klaver wrote: On 10/22/22 09:41, Ron wrote: On 10/22/22 11:20, Adrian Klaver wrote: On 10/20/22 14:34, Ron wrote: On 10/20/22 10:02, Adrian Klaver wrote: On 10/20/22 06:20, Ron wrote: On 10/20/22 00:12, Tom Lane wrote: I was afraid you were going to say that. The work-around is to: pg_dump $SRCDB --schema-only | grep -e '^\(GRANT|REVOKE\)' > all_GRANT.sql pg_dump $SRCDB --schema-only | grep OWNER > all_OWNER.sql pg_restore --jobs=X --no-owner $NEWDB The above and below have me confused. What is $NEWDB? In above it seems to be a file and below a database name. Consider it pseudo-code. psql $NEWDB -f all_OWNER.sql psql $NEWDB -f all_GRANT.sql This is, of course, why we need to test the backup/restore process. -- Angular momentum makes the world go 'round.
Re: pg_restore 12 "permission denied for schema" errors
On 10/22/22 14:02, Ron wrote: On 10/22/22 12:00, Adrian Klaver wrote: On 10/22/22 09:41, Ron wrote: On 10/22/22 11:20, Adrian Klaver wrote: On 10/20/22 14:34, Ron wrote: On 10/20/22 10:02, Adrian Klaver wrote: On 10/20/22 06:20, Ron wrote: On 10/20/22 00:12, Tom Lane wrote: I was afraid you were going to say that. The work-around is to: pg_dump $SRCDB --schema-only | grep -e '^\(GRANT|REVOKE\)' > all_GRANT.sql pg_dump $SRCDB --schema-only | grep OWNER > all_OWNER.sql pg_restore --jobs=X --no-owner $NEWDB The above and below have me confused. What is $NEWDB? In above it seems to be a file and below a database name. Consider it pseudo-code. To pseudo for me. What file exactly is: pg_restore --jobs=X --no-owner $NEWDB restoring? And how was that file created? Knowing this might help get at why the more straight forward method does not work. psql $NEWDB -f all_OWNER.sql psql $NEWDB -f all_GRANT.sql This is, of course, why we need to test the backup/restore process. -- Adrian Klaver adrian.kla...@aklaver.com
Re: pg_restore 12 "permission denied for schema" errors
On 10/22/22 16:29, Adrian Klaver wrote:
On 10/22/22 14:02, Ron wrote:
On 10/22/22 12:00, Adrian Klaver wrote:
On 10/22/22 09:41, Ron wrote:
On 10/22/22 11:20, Adrian Klaver wrote:
On 10/20/22 14:34, Ron wrote:
On 10/20/22 10:02, Adrian Klaver wrote:
On 10/20/22 06:20, Ron wrote:
On 10/20/22 00:12, Tom Lane wrote:
I was afraid you were going to say that.
The work-around is to:
pg_dump $SRCDB --schema-only | grep -e '^\(GRANT|REVOKE\)' > all_GRANT.sql
pg_dump $SRCDB --schema-only | grep OWNER > all_OWNER.sql
pg_restore --jobs=X --no-owner $NEWDB
The above and below have me confused.
What is $NEWDB?
In above it seems to be a file and below a database name.
Consider it pseudo-code.
To pseudo for me.
What file exactly is:
pg_restore --jobs=X --no-owner $NEWDB
restoring?
And how was that file created?
Knowing this might help get at why the more straight forward method does
not work.
This is what I ran to restore the database:
export PGHOST=${RDSENV}..us-east-1.rds.amazonaws.com
cd /migrate/TASK001793786/2022-10-19b
NEWDB=sides
pg_restore -v --create --clean --no-owner --jobs=`nproc` -Fd
--dbname=template1 $NEWDB
psql $NEWDB -f all_OWNER.sql
psql $NEWDB -f all_GRANT.sql
The name of the database is "sides", and there's a directorynamed "sides"
under /migrate/TASK001793786/2022-10-19b.
psql $NEWDB -f all_OWNER.sql
psql $NEWDB -f all_GRANT.sql
This is, of course, why we need to test the backup/restore process.
--
Angular momentum makes the world go 'round.
Re: pg_restore 12 "permission denied for schema" errors
On 10/22/22 14:45, Ron wrote:
On 10/22/22 16:29, Adrian Klaver wrote:
To pseudo for me.
What file exactly is:
pg_restore --jobs=X --no-owner $NEWDB
restoring?
And how was that file created?
Knowing this might help get at why the more straight forward method
does not work.
This is what I ran to restore the database:
export PGHOST=${RDSENV}..us-east-1.rds.amazonaws.com
cd /migrate/TASK001793786/2022-10-19b
NEWDB=sides
pg_restore -v --create --clean --no-owner --jobs=`nproc` -Fd
--dbname=template1 $NEWDB
psql $NEWDB -f all_OWNER.sql
psql $NEWDB -f all_GRANT.sql
The name of the database is "sides", and there's a directorynamed
"sides" under /migrate/TASK001793786/2022-10-19b.
Aah, I forgot about the -Fd, now it makes more sense.
To get past the --jobs induced error required the addition of --no-owner
and then adding owners and grants after the main restore.
What was the pg_dump command that produced
/migrate/TASK001793786/2022-10-19b/sides ?
psql $NEWDB -f all_OWNER.sql
psql $NEWDB -f all_GRANT.sql
This is, of course, why we need to test the backup/restore process.
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: pg_restore 12 "permission denied for schema" errors
On 10/22/22 17:06, Adrian Klaver wrote:
On 10/22/22 14:45, Ron wrote:
On 10/22/22 16:29, Adrian Klaver wrote:
To pseudo for me.
What file exactly is:
pg_restore --jobs=X --no-owner $NEWDB
restoring?
And how was that file created?
Knowing this might help get at why the more straight forward method does
not work.
This is what I ran to restore the database:
export PGHOST=${RDSENV}..us-east-1.rds.amazonaws.com
cd /migrate/TASK001793786/2022-10-19b
NEWDB=sides
pg_restore -v --create --clean --no-owner --jobs=`nproc` -Fd
--dbname=template1 $NEWDB
psql $NEWDB -f all_OWNER.sql
psql $NEWDB -f all_GRANT.sql
The name of the database is "sides", and there's a directorynamed "sides"
under /migrate/TASK001793786/2022-10-19b.
Aah, I forgot about the -Fd, now it makes more sense.
To get past the --jobs induced error required the addition of --no-owner
and then adding owners and grants after the main restore.
What was the pg_dump command that produced
/migrate/TASK001793786/2022-10-19b/sides ?
PGHOST=mumble..us-east-1.rds.amazonaws.com
PGUSER=postgres
cd /migrate/TASK001793786/`date +%F`
DB=sides
pg_dump -d $DB -j4 -Z0 -v -Fd --file=$DB 2> ${DB}_pgdump.log
--
Angular momentum makes the world go 'round.
Re: pg_restore 12 "permission denied for schema" errors
Ron writes: > On 10/20/22 10:02, Adrian Klaver wrote: >> 3) What if you run without --jobs? > It runs without error. Add "--jobs=2" and the errors appear. That's ... suggestive ... but not suggestive enough. Can you create a self-contained test case? It probably doesn't depend much at all on your data, just the schema. regards, tom lane
