Reset DB stats suggestion pg_stat_reset()
Hi Team, Good Afternoon, We have seen some deadlocks and tempfile count in pg_stat_database view. We are trying to reset the stats. Can we use pg_stat_reset() function to reset these stats without any impact stats of databases. Please advise the process to reset the stats. Thanks. Postggen.
Re: Reset DB stats suggestion pg_stat_reset()
On 2/22/20 12:26 AM, postggen2020 s wrote: Hi Team, Good Afternoon, We have seen some deadlocks and tempfile count in pg_stat_database view. We are trying to reset the stats. Can we use pg_stat_reset() function to reset these stats without any impact stats of databases. Please advise the process to reset the stats. See here: https://www.postgresql.org/docs/12/monitoring-stats.html Table 27.20. Additional Statistics Functions It details what your options are. Thanks. Postggen. -- Adrian Klaver adrian.kla...@aklaver.com
Replication: slave server has 3x size of production server?
Hi! I've a database cluster created at 9.6.10 linux x64 server rhel. I made progressive upgrades, first upgrading slave and then upgrading master. Actually both are running 9.6.17. Current production server has 196Gb in size. Nevertheless, the replicated (slave) server has 598 Gb in size. Replication server has 3x size of production server, is that normal? Shall I drop the slave server and re-create it? How to avoid this situation in future? Thanks, Edson
Re: Shared buffer hash table corrupted
On Fri, Feb 21, 2020 at 2:53 PM Tom Lane wrote: > > Personally, I'd restart the postmaster, but not do more than that unless > the error recurs. > Thanks for the response. I did restart the postmaster yesterday. Earlier this morning, a query that normally completes fine started to error out with 'invalid memory alloc request size 18446744073709551613'. Needless to say our database isn't quite that size. This query was against a table in a different database than the one that had the corruption warning yesterday. Restarting the postmaster again fixed the problem. For good measure I restarted the machine as well. I need to decide what to do next, if anything. We have a hot standby that we also run queries against, and it hasn't shown any errors. I can switch over to that as the primary. Or I can move the main database to a different physical host. Thoughts appreciated. Thanks, Mark
Re: Replication: slave server has 3x size of production server?
On 2/22/20 9:25 AM, Edson Richter wrote: Hi! I've a database cluster created at 9.6.10 linux x64 server rhel. I made progressive upgrades, first upgrading slave and then upgrading master. Actually both are running 9.6.17. Current production server has 196Gb in size. Nevertheless, the replicated (slave) server has 598 Gb in size. Replication server has 3x size of production server, is that normal? How are you measuring the sizes? Where is the space being taken up on disk? Shall I drop the slave server and re-create it? How to avoid this situation in future? Thanks, Edson -- Adrian Klaver adrian.kla...@aklaver.com
Re: Shared buffer hash table corrupted
Mark Fletcher writes: > Thanks for the response. I did restart the postmaster yesterday. Earlier > this morning, a query that normally completes fine started to error out > with 'invalid memory alloc request size 18446744073709551613'. Needless to > say our database isn't quite that size. This query was against a table in a > different database than the one that had the corruption warning yesterday. > Restarting the postmaster again fixed the problem. For good measure I > restarted the machine as well. Um. At that point I'd agree with your concern about developing hardware problems. Both of these symptoms could be easily explained by dropped bits in PG's shared memory area. Do you happen to know if the server has ECC RAM? regards, tom lane
Re: Shared buffer hash table corrupted
On Sat, Feb 22, 2020 at 9:34 AM Tom Lane wrote: > > Um. At that point I'd agree with your concern about developing hardware > problems. Both of these symptoms could be easily explained by dropped > bits in PG's shared memory area. Do you happen to know if the server > has ECC RAM? > > Yes, it appears that Linode uses ECC and other server grade hardware for their machines. Thanks, Mark
RE: Replication: slave server has 3x size of production server?
De: Adrian Klaver Enviado: sábado, 22 de fevereiro de 2020 14:33 Para: Edson Richter ; pgsql-general Assunto: Re: Replication: slave server has 3x size of production server? On 2/22/20 9:25 AM, Edson Richter wrote: > Hi! > > I've a database cluster created at 9.6.10 linux x64 server rhel. I made > progressive upgrades, first upgrading slave and then upgrading master. > Actually both are running 9.6.17. > Current production server has 196Gb in size. > Nevertheless, the replicated (slave) server has 598 Gb in size. > Replication server has 3x size of production server, is that normal? How are you measuring the sizes? This is the command: du --max-depth 1 -h pgDbCluster Production: du --max-depth 1 -h pgDbCluster 56M pgDbCluster/pg_log 444KpgDbCluster/global 4,0KpgDbCluster/pg_stat 4,0KpgDbCluster/pg_snapshots 16K pgDbCluster/pg_logical 20K pgDbCluster/pg_replslot 61M pgDbCluster/pg_subtrans 4,0KpgDbCluster/pg_commit_ts 465MpgDbCluster/pg_xlog 4,0KpgDbCluster/pg_twophase 12M pgDbCluster/pg_multixact 4,0KpgDbCluster/pg_serial 195GpgDbCluster/base 284KpgDbCluster/pg_stat_tmp 12M pgDbCluster/pg_clog 4,0KpgDbCluster/pg_dynshmem 12K pgDbCluster/pg_notify 4,0KpgDbCluster/pg_tblspc 196GpgDbCluster Slave: du -h --max-depth 1 pgDbCluster 403GpgDbCluster/pg_xlog 120KpgDbCluster/pg_log 424KpgDbCluster/global 0 pgDbCluster/pg_stat 0 pgDbCluster/pg_snapshots 4,0KpgDbCluster/pg_logical 8,0KpgDbCluster/pg_replslot 60M pgDbCluster/pg_subtrans 0 pgDbCluster/pg_commit_ts 0 pgDbCluster/pg_twophase 11M pgDbCluster/pg_multixact 0 pgDbCluster/pg_serial 195GpgDbCluster/base 12M pgDbCluster/pg_clog 0 pgDbCluster/pg_dynshmem 8,0KpgDbCluster/pg_notify 12K pgDbCluster/pg_stat_tmp 0 pgDbCluster/pg_tblspc 598GpgDbCluster Edson Where is the space being taken up on disk? > > Shall I drop the slave server and re-create it? How to avoid this > situation in future? > > Thanks, > > Edson > > -- Adrian Klaver adrian.kla...@aklaver.com
how to find a tablespace for the table?
Hi team, how to find a tablespace for the table? See my comments below: I have created a database with default tablespace like below: edb=# CREATE DATABASE conndb WITH TABLESPACE = conn_s_tables; After that I have created a table CREATE TABLE COMPANY_new( ID INT PRIMARY KEY NOT NULL, NAME TEXTNOT NULL, AGEINT NOT NULL, ADDRESSCHAR(50), SALARY REAL, JOIN_DATEDATE ) , CREATE TABLE COMPANY_new( ID INT PRIMARY KEY NOT NULL, NAME TEXTNOT NULL, AGEINT NOT NULL, ADDRESSCHAR(50), SALARY REAL, JOIN_DATEDATE ) tablespace conn_s_tables ; But I am unable to search the tablespace name where tablespace exist , tablespace column is blank. conndb=# select schemaname,tablename,tableowner,tablespace from pg_tables where tablename='company'; schemaname | tablename | tableowner | tablespace +---+--+ conndb | company | enterprisedb | (1 row) conndb=# select schemaname,tablename,tableowner,tablespace from pg_tables where tablename='company_new'; schemaname | tablename | tableowner | tablespace +-+--+ conndb | company_new | enterprisedb |
Re: how to find a tablespace for the table?
On 2/22/20 10:34 AM, Daulat Ram wrote: Hi team, how to find a tablespace for the table? See my comments below: I have created a database with default tablespace like below: edb=# CREATE DATABASE conndb WITH TABLESPACE = conn_s_tables; After that I have created a table CREATE TABLE COMPANY_new( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL, JOIN_DATE DATE ) , CREATE TABLE COMPANY_new( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL, JOIN_DATE DATE ) tablespace conn_s_tables ; But I am unable to search the tablespace name where tablespace exist , tablespace column is blank. https://www.postgresql.org/docs/12/view-pg-tables.html tablespace name pg_tablespace.spcname Name of tablespace containing table (null if default for database) https://www.postgresql.org/docs/12/sql-createdatabase.html tablespace_name The name of the tablespace that will be associated with the new database, or DEFAULT to use the template database's tablespace. This tablespace will be the default tablespace used for objects created in this database. See CREATE TABLESPACE for more information. So conn_s_tables is default for conndb, therefore it will not show up in queries below. If you want to find the default tablespace: https://www.postgresql.org/docs/12/catalog-pg-database.html dattablespace oid pg_tablespace.oid The default tablespace for the database. Within this database, all tables for which pg_class.reltablespace is zero will be stored in this tablespace; in particular, all the non-shared system catalogs will be there. conndb=# select schemaname,tablename,tableowner,tablespace from pg_tables where tablename='company'; schemaname | tablename | tableowner | tablespace +---+--+ conndb | company | enterprisedb | (1 row) conndb=# select schemaname,tablename,tableowner,tablespace from pg_tables where tablename='company_new'; schemaname | tablename | tableowner | tablespace +-+--+ conndb | company_new | enterprisedb | -- Adrian Klaver adrian.kla...@aklaver.com
Re: Replication: slave server has 3x size of production server?
On 2/22/20 10:05 AM, Edson Richter wrote: *De:* Adrian Klaver *Enviado:* sábado, 22 de fevereiro de 2020 14:33 *Para:* Edson Richter ; pgsql-general *Assunto:* Re: Replication: slave server has 3x size of production server? On 2/22/20 9:25 AM, Edson Richter wrote: > Hi! > > I've a database cluster created at 9.6.10 linux x64 server rhel. I made > progressive upgrades, first upgrading slave and then upgrading master. > Actually both are running 9.6.17. > Current production server has 196Gb in size. > Nevertheless, the replicated (slave) server has 598 Gb in size. > Replication server has 3x size of production server, is that normal? How are you measuring the sizes? This is the command: du --max-depth 1 -h pgDbCluster Production: du --max-depth 1 -h pgDbCluster 56M pgDbCluster/pg_log 444K pgDbCluster/global 4,0K pgDbCluster/pg_stat 4,0K pgDbCluster/pg_snapshots 16K pgDbCluster/pg_logical 20K pgDbCluster/pg_replslot 61M pgDbCluster/pg_subtrans 4,0K pgDbCluster/pg_commit_ts 465M pgDbCluster/pg_xlog 4,0K pgDbCluster/pg_twophase 12M pgDbCluster/pg_multixact 4,0K pgDbCluster/pg_serial 195G pgDbCluster/base 284K pgDbCluster/pg_stat_tmp 12M pgDbCluster/pg_clog 4,0K pgDbCluster/pg_dynshmem 12K pgDbCluster/pg_notify 4,0K pgDbCluster/pg_tblspc 196G pgDbCluster Slave: du -h --max-depth 1 pgDbCluster 403G pgDbCluster/pg_xlog 120K pgDbCluster/pg_log 424K pgDbCluster/global 0 pgDbCluster/pg_stat 0 pgDbCluster/pg_snapshots 4,0K pgDbCluster/pg_logical 8,0K pgDbCluster/pg_replslot 60M pgDbCluster/pg_subtrans 0 pgDbCluster/pg_commit_ts 0 pgDbCluster/pg_twophase 11M pgDbCluster/pg_multixact 0 pgDbCluster/pg_serial 195G pgDbCluster/base 12M pgDbCluster/pg_clog 0 pgDbCluster/pg_dynshmem 8,0K pgDbCluster/pg_notify 12K pgDbCluster/pg_stat_tmp 0 pgDbCluster/pg_tblspc 598G pgDbCluster So the WAL logs are not being cleared. What replication method is being used? What are the settings for the replication? Edson -- Adrian Klaver adrian.kla...@aklaver.com
RE: Replication: slave server has 3x size of production server?
De: Adrian Klaver Enviado: sábado, 22 de fevereiro de 2020 15:50 Para: Edson Richter ; pgsql-general Assunto: Re: Replication: slave server has 3x size of production server? On 2/22/20 10:05 AM, Edson Richter wrote: > > > *De:* Adrian Klaver > *Enviado:* sábado, 22 de fevereiro de 2020 14:33 > *Para:* Edson Richter ; pgsql-general > > *Assunto:* Re: Replication: slave server has 3x size of production > server? > On 2/22/20 9:25 AM, Edson Richter wrote: > > Hi! > > > > I've a database cluster created at 9.6.10 linux x64 server rhel. I made > > progressive upgrades, first upgrading slave and then upgrading master. > > Actually both are running 9.6.17. > > Current production server has 196Gb in size. > > Nevertheless, the replicated (slave) server has 598 Gb in size. > > Replication server has 3x size of production server, is that normal? > > How are you measuring the sizes? > > > This is the command: > > du --max-depth 1 -h pgDbCluster > > > Production: > > du --max-depth 1 -h pgDbCluster > > 56M pgDbCluster/pg_log > 444KpgDbCluster/global > 4,0KpgDbCluster/pg_stat > 4,0KpgDbCluster/pg_snapshots > 16K pgDbCluster/pg_logical > 20K pgDbCluster/pg_replslot > 61M pgDbCluster/pg_subtrans > 4,0KpgDbCluster/pg_commit_ts > 465MpgDbCluster/pg_xlog > 4,0KpgDbCluster/pg_twophase > 12M pgDbCluster/pg_multixact > 4,0KpgDbCluster/pg_serial > 195GpgDbCluster/base > 284KpgDbCluster/pg_stat_tmp > 12M pgDbCluster/pg_clog > 4,0KpgDbCluster/pg_dynshmem > 12K pgDbCluster/pg_notify > 4,0KpgDbCluster/pg_tblspc > 196GpgDbCluster > > > Slave: > > du -h --max-depth 1 pgDbCluster > > 403GpgDbCluster/pg_xlog > 120KpgDbCluster/pg_log > 424KpgDbCluster/global > 0 pgDbCluster/pg_stat > 0 pgDbCluster/pg_snapshots > 4,0KpgDbCluster/pg_logical > 8,0KpgDbCluster/pg_replslot > 60M pgDbCluster/pg_subtrans > 0 pgDbCluster/pg_commit_ts > 0 pgDbCluster/pg_twophase > 11M pgDbCluster/pg_multixact > 0 pgDbCluster/pg_serial > 195GpgDbCluster/base > 12M pgDbCluster/pg_clog > 0 pgDbCluster/pg_dynshmem > 8,0KpgDbCluster/pg_notify > 12K pgDbCluster/pg_stat_tmp > 0 pgDbCluster/pg_tblspc > 598GpgDbCluster So the WAL logs are not being cleared. What replication method is being used? What are the settings for the replication? Streaming replication. Initiated via pg_basebackup. Settings on master server: # - Sending Server(s) - # Set these on the master and on any standby that will send replication data. max_wal_senders = 2 # max number of walsender processes (change requires restart) wal_keep_segments = 25 # in logfile segments, 16MB each; 0 disables #wal_sender_timeout = 60s # in milliseconds; 0 disables max_replication_slots = 2 # max number of replication slots (change requires restart) #track_commit_timestamp = off # collect timestamp of transaction commit (change requires restart) # - Master Server - # These settings are ignored on a standby server. #synchronous_standby_names = '' # standby servers that provide sync rep number of sync standbys and comma-separated list of application_name from standby(s); '*' = all #vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed Settings on slave server: # - Standby Servers - # These settings are ignored on a master server. hot_standby = on# "on" allows queries during recovery (change requires restart) max_standby_archive_delay = -1 # max delay before canceling queries when reading WAL from archive; -1 allows indefinite delay max_standby_streaming_delay = -1# max delay before canceling queries when reading streaming WAL; -1 allows indefinite delay wal_receiver_status_interval = 10s # send replies at least this often 0 disables hot_standby_feedback = on # send info from standby to prevent query conflicts wal_receiver_timeout = 0# time that receiver waits for communication from master in milliseconds; 0 disables wal_retrieve_retry_interval = 5s# time to wait before retrying to retrieve WAL after a failed attempt Regards, Edson > > > Edson > -- Adrian Klaver adrian.kla...@aklaver.com
Re: Replication: slave server has 3x size of production server?
On 2/22/20 11:03 AM, Edson Richter wrote: Streaming replication. Initiated via pg_basebackup. Settings on master server: # - Sending Server(s) - # Set these on the master and on any standby that will send replication data. max_wal_senders = 2 # max number of walsender processes (change requires restart) wal_keep_segments = 25 # in logfile segments, 16MB each; 0 disables #wal_sender_timeout = 60s # in milliseconds; 0 disables max_replication_slots = 2 # max number of replication slots (change requires restart) #track_commit_timestamp = off # collect timestamp of transaction commit (change requires restart) # - Master Server - # These settings are ignored on a standby server. #synchronous_standby_names = '' # standby servers that provide sync rep number of sync standbys and comma-separated list of application_name from standby(s); '*' = all #vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed Settings on slave server: # - Standby Servers - # These settings are ignored on a master server. hot_standby = on # "on" allows queries during recovery (change requires restart) max_standby_archive_delay = -1 # max delay before canceling queries when reading WAL from archive; -1 allows indefinite delay max_standby_streaming_delay = -1 # max delay before canceling queries when reading streaming WAL; -1 allows indefinite delay wal_receiver_status_interval = 10s # send replies at least this often 0 disables hot_standby_feedback = on # send info from standby to prevent query conflicts wal_receiver_timeout = 0 # time that receiver waits for communication from master in milliseconds; 0 disables wal_retrieve_retry_interval = 5s # time to wait before retrying to retrieve WAL after a failed attempt What are the settings for: archive_mode archive_command on the standby? Are the files in pg_xlog on the standby mostly from well in the past? Regards, Edson > > > Edson > -- Adrian Klaver adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com
RE: Replication: slave server has 3x size of production server?
De: Adrian Klaver Enviado: sábado, 22 de fevereiro de 2020 16:16 Para: Edson Richter ; pgsql-general Assunto: Re: Replication: slave server has 3x size of production server? On 2/22/20 11:03 AM, Edson Richter wrote: > > > > > Streaming replication. Initiated via pg_basebackup. > > Settings on master server: > > # - Sending Server(s) - > # Set these on the master and on any standby that will send replication > data. > max_wal_senders = 2 # max number of walsender processes > (change requires restart) > wal_keep_segments = 25 # in logfile segments, 16MB each; 0 disables > #wal_sender_timeout = 60s # in milliseconds; 0 disables > max_replication_slots = 2 # max number of replication > slots (change requires restart) > #track_commit_timestamp = off # collect timestamp of transaction > commit (change requires restart) > # - Master Server - > # These settings are ignored on a standby server. > #synchronous_standby_names = '' # standby servers that provide sync > rep number of sync standbys and comma-separated list of > application_name from standby(s); '*' = all > #vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is > delayed > > > > Settings on slave server: > > # - Standby Servers - > # These settings are ignored on a master server. > hot_standby = on# "on" allows queries during > recovery (change requires restart) > max_standby_archive_delay = -1 # max delay before canceling > queries when reading WAL from archive; -1 allows indefinite delay > max_standby_streaming_delay = -1# max delay before canceling > queries when reading streaming WAL; -1 allows indefinite delay > wal_receiver_status_interval = 10s # send replies at least this > often 0 disables > hot_standby_feedback = on # send info from standby to > prevent query conflicts > wal_receiver_timeout = 0# time that receiver waits for > communication from master in milliseconds; 0 disables > wal_retrieve_retry_interval = 5s# time to wait before retrying > to retrieve WAL after a failed attempt What are the settings for: archive_mode archive_command on the standby? Are the files in pg_xlog on the standby mostly from well in the past? Actually, standby server is sending wals to a backup (barman) server: archive_mode = always # enables archiving; off, on, or always (change requires restart) archive_command = 'rsync -e "ssh -2 -C -p 2022" -az %p barman@192.168.0.2:/dados/barman/dbcluster/incoming/%f' The files are about 7 months old. Thanks, Edson > > > Regards, > > Edson > > > > > > > Edson > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- Adrian Klaver adrian.kla...@aklaver.com
Can I trigger an action from a coalesce ?
I have a case where if a value does not exist, I am going to use a default, which is easy with coalesce. But I would like to warn the user that a default has been supplied. The default value is reasonable, and could actually come from the source table, so I can't just check the value. I'd like to do a raise NOTICE, if the default portion of the coalesce fires. Anyone have a good way to accomplish this? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: Can I trigger an action from a coalesce ?
On 2/22/20 1:02 PM, stan wrote: I have a case where if a value does not exist, I am going to use a default, which is easy with coalesce. But I would like to warn the user that a default has been supplied. The default value is reasonable, and could actually come from the source table, so I can't just check the value. I'd like to do a raise NOTICE, if the default portion of the coalesce fires. Anyone have a good way to accomplish this? No. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Can I trigger an action from a coalesce ?
> On Feb 22, 2020, at 13:05, Adrian Klaver wrote: > > On 2/22/20 1:02 PM, stan wrote: >> I have a case where if a value does not exist, I am going to use a default, >> which is easy with coalesce. But I would like to warn the user that a >> default has been supplied. The default value is reasonable, and could >> actually come from the source table, so I can't just check the value. >> I'd like to do a raise NOTICE, if the default portion of the coalesce fires. >> Anyone have a good way to accomplish this? > > No. You can, of course, create a PL/pgSQL function and use that as the default. -- -- Christophe Pettus x...@thebuild.com
Re: How to fix 0xC0000005 exception in Postgres 9.0
On 2/21/20 11:43 PM, Andrus wrote: Hi! To me the relevant part of the log is below. Not sure what it means though: [3604-0.187] execute.c[Exec_with_parameters_resolved]444: stmt_with_params = ';SELECT * FROM temptulemus offset 0 limit 900' [3604-0.187] execute.c[Exec_with_parameters_resolved]449: about to begin SC_execute [3604-0.187]statement.[SC_execute]2037: it's NOT a select statement: stmt=005FE040 Maybe issue occurs when ODBC client sends command to odbc driver which contains semicolon as first character before SELECT and returned data I have no idea. For psqlODBC issues I would suggest asking here: https://www.postgresql.org/list/pgsql-odbc/ Chances are better that there will be someone there that could answer you questions. contains certain characters and server or client has certain configuration. Maybe I will change application not to send semicolon before SELECT statement. Andrus. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Replication: slave server has 3x size of production server?
On 2/22/20 11:23 AM, Edson Richter wrote: *De:* Adrian Klaver *Enviado:* sábado, 22 de fevereiro de 2020 16:16 *Para:* Edson Richter ; pgsql-general *Assunto:* Re: Replication: slave server has 3x size of production server? On 2/22/20 11:03 AM, Edson Richter wrote: > > > > > Streaming replication. Initiated via pg_basebackup. > > Settings on master server: > > # - Sending Server(s) - > # Set these on the master and on any standby that will send replication > data. > max_wal_senders = 2 # max number of walsender processes > (change requires restart) > wal_keep_segments = 25 # in logfile segments, 16MB each; 0 disables > #wal_sender_timeout = 60s # in milliseconds; 0 disables > max_replication_slots = 2 # max number of replication > slots (change requires restart) > #track_commit_timestamp = off # collect timestamp of transaction > commit (change requires restart) > # - Master Server - > # These settings are ignored on a standby server. > #synchronous_standby_names = '' # standby servers that provide sync > rep number of sync standbys and comma-separated list of > application_name from standby(s); '*' = all > #vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is > delayed > > > > Settings on slave server: > > # - Standby Servers - > # These settings are ignored on a master server. > hot_standby = on # "on" allows queries during > recovery (change requires restart) > max_standby_archive_delay = -1 # max delay before canceling > queries when reading WAL from archive; -1 allows indefinite delay > max_standby_streaming_delay = -1 # max delay before canceling > queries when reading streaming WAL; -1 allows indefinite delay > wal_receiver_status_interval = 10s # send replies at least this > often 0 disables > hot_standby_feedback = on # send info from standby to > prevent query conflicts > wal_receiver_timeout = 0 # time that receiver waits for > communication from master in milliseconds; 0 disables > wal_retrieve_retry_interval = 5s # time to wait before retrying > to retrieve WAL after a failed attempt What are the settings for: archive_mode archive_command on the standby? Are the files in pg_xlog on the standby mostly from well in the past? Actually, standby server is sending wals to a backup (barman) server: archive_mode = always # enables archiving; off, on, or always (change requires restart) archive_command = 'rsync -e "ssh -2 -C -p 2022" -az %p barman@192.168.0.2:/dados/barman/dbcluster/incoming/%f' And the above is working, the files are showing up on the barman server? The files are about 7 months old. Are there newer files that would indicate that the streaming is working? Thanks, Edson > > > Regards, > > Edson > > > > > > > Edson > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- Adrian Klaver adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com
Re: Can I trigger an action from a coalesce ?
On Sat, Feb 22, 2020 at 01:06:57PM -0800, Christophe Pettus wrote: > > > > On Feb 22, 2020, at 13:05, Adrian Klaver wrote: > > > > On 2/22/20 1:02 PM, stan wrote: > >> I have a case where if a value does not exist, I am going to use a default, > >> which is easy with coalesce. But I would like to warn the user that a > >> default has been supplied. The default value is reasonable, and could > >> actually come from the source table, so I can't just check the value. > >> I'd like to do a raise NOTICE, if the default portion of the coalesce > >> fires. > >> Anyone have a good way to accomplish this? > > > > No. > > You can, of course, create a PL/pgSQL function and use that as the default. I suppose you are suggesting that the function try the original SELECT, and if it returns a NULL then retun the default AND do the raise NOTICE? Or is there a simpler way? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Re: Can I trigger an action from a coalesce ?
> On Feb 22, 2020, at 13:33, stan wrote:
> I suppose you are suggesting that the function try the original SELECT, and
> if it returns a NULL then retun the default AND do the raise NOTICE?
Something like this:
create function supply_default() returns int as $$
begin
raise notice 'Supplied default';
return 1;
end;
$$ immutable language plpgsql;
xof=# create table t ( i integer default supply_default(), t text );
CREATE TABLE
xof=# insert into t(i, t) values (2, 'text');
INSERT 0 1
xof=# insert into t(t) values ('text');
NOTICE: Supplied default
INSERT 0 1
--
-- Christophe Pettus
x...@thebuild.com
Re: Can I trigger an action from a coalesce ?
Christophe Pettus writes:
> Something like this:
> create function supply_default() returns int as $$
> begin
>raise notice 'Supplied default';
>return 1;
> end;
> $$ immutable language plpgsql;
It's a really bad idea to mark a function that has side-effects
(i.e., emitting a NOTICE) as immutable, especially if the occurrence
of the side-effect at well-defined times is exactly what you're
desirous of.
> xof=# create table t ( i integer default supply_default(), t text );
> CREATE TABLE
> xof=# insert into t(i, t) values (2, 'text');
> INSERT 0 1
> xof=# insert into t(t) values ('text');
> NOTICE: Supplied default
> INSERT 0 1
Other than the mislabeled volatility, I think this will mostly work.
Another possibility is to use a before-row-insert trigger
that does something like
if new.i is null then
begin
new.i := whatever;
raise notice 'Supplied default';
end if;
This seems cleaner in principle, but a problem is that it can't tell
an inserted-by-default NULL from one that was intentionally supplied.
That might be OK if you never want the field to be null anyway.
regards, tom lane
Re: Can I trigger an action from a coalesce ?
> On Feb 22, 2020, at 14:02, Tom Lane wrote: > It's a really bad idea to mark a function that has side-effects > (i.e., emitting a NOTICE) as immutable, especially if the occurrence > of the side-effect at well-defined times is exactly what you're > desirous of. True, and it doesn't actually need to be immutable here; just cut and pasted from the wrong example. (That being said, I'm not coming up with a specific bad thing that a RAISE NOTICE in an immutable function will cause. Is there one?) -- -- Christophe Pettus x...@thebuild.com
Re: Logical replication lag in seconds
Hi Michael! Am 21.02.2020 um 21:24 schrieb Michael Lewis: I am very interested in this discussion. We settled a table with a single timestamp field that a script updates every minute with NOW() so that we can check the timestamp of that table on the replica, assuming the clocks are synced, then we will be able to compute the lag. I have a similar workaround at the moment. But it is more a hack than a nice solution, ie. I also have to store the last value locally to have the status also available if a replica is temporarily not reachable. Hence it would be great if the information could be retrieved from WAL/replication internals. regards Klaus
Re: Cannot connect to postgresql-11 from another machine after boot
On 2020-02-17 10:17:41 -0500, Jason Swails wrote: > I then added "network.target", "networking.service", and > "network-online.target" to the After line of the postgresql.service systemd > file, but it still didn't fix the problem. After=network-online.target should be correct. However, see https://www.freedesktop.org/wiki/Software/systemd/NetworkTarget/ for an explanation why "the network is online" is not as simple as it looks and how to ensure that. 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
Re: Can I trigger an action from a coalesce ?
Christophe Pettus writes: >> On Feb 22, 2020, at 14:02, Tom Lane wrote: >> It's a really bad idea to mark a function that has side-effects >> (i.e., emitting a NOTICE) as immutable, especially if the occurrence >> of the side-effect at well-defined times is exactly what you're >> desirous of. > True, and it doesn't actually need to be immutable here; just cut and pasted > from the wrong example. > (That being said, I'm not coming up with a specific bad thing that a RAISE > NOTICE in an immutable function will cause. Is there one?) The problem that I'm worried about is premature evaluation of the "immutable" function, causing the NOTICE to come out once during query planning, independently of whether/how many times it should come out during execution. regards, tom lane
Re: Can I trigger an action from a coalesce ?
> On Feb 22, 2020, at 14:36, Tom Lane wrote: > The problem that I'm worried about is premature evaluation of the > "immutable" function, causing the NOTICE to come out once during > query planning, independently of whether/how many times it should > come out during execution. Ah, good point. My solution also does assume that a DEFAULT expression is only evaluated if the default is required, and that behavior isn't (afaik) a promise. -- -- Christophe Pettus x...@thebuild.com
How to get error message details from libpq based psqlODBC driver (regression)
Hi! I'm looking for a way to fix psqlODBC driver regression. Starting at psqlODBC 09.05.0100 when psqlODBC driver uses libpq for all operations (earlier versions used libpg only for authentication) ODBC client does not show error message details. For example, users got only generic error message like Connectivity error: ERROR: insert or update on table "mytable" violates foreign key constraint "mytable_myfield_fkey Error message details like Key (myfield)=(mykeyvalue) is not present in table "mymastertable".; are no more returned. How to detailed error message or at least only bad key value "mykeyvalue" which is returned in error message details ? Some ideas: 1. First versions of libpq based ODBC drivers returned same error message "no resource found" for all errors. Hiroshi has fixed it it later versions. Maybe psqlODBC code can fixed to restore pre-libpq behaviour. 2. Maybe analyzing odbc logs from pre and after 09.05.0100 drivers may provide solution. I can provide ODBC log files. 3. psqlODBC allows to pass parameters to libpq from connection string. Maybe some libpq parameter can fix this. 4. Maybe some Postgres query, postgres extension or direct odbc or libpq call can used to return last error message details like Windows API GetLastError() or Unix global errno. 5. Maybe it is possible to to create method which returns error message detals from postgres log file. Postgres 12.2 and latest psqlODBC driver 12.01. are used. psqlODBC is called from Visual FoxPro Andrus.
Re: How to fix 0xC0000005 exception in Postgres 9.0
Hi! I have no idea. I changed application not to send ; before select. This solves issues in both servers. So using ; as first character before select in ODBC command like ;SELECT * from mytable Causes C5 is Postgres 9.0 and ODBC client hangup with "connection reset by peer" message in log file in Postgres 9.6 It was great help and especially great ODBC log analyzing in this list, thanks. For psqlODBC issues I would suggest asking here: https://www.postgresql.org/list/pgsql-odbc/ Chances are better that there will be someone there that could answer you questions. I posted error message details issue long time ago in this pgsql-odbc list but havent got solution. I posted it as separate message here and in https://stackoverflow.com/questions/60357505/how-to-fix-psqlodbc-driver-regression-to-get-error-message-details Andrus.
RE: Replication: slave server has 3x size of production server?
De: Adrian Klaver Enviado: sábado, 22 de fevereiro de 2020 18:12 Para: Edson Richter ; pgsql-general Assunto: Re: Replication: slave server has 3x size of production server? On 2/22/20 11:23 AM, Edson Richter wrote: > > > *De:* Adrian Klaver > *Enviado:* sábado, 22 de fevereiro de 2020 16:16 > *Para:* Edson Richter ; pgsql-general > > *Assunto:* Re: Replication: slave server has 3x size of production > server? > On 2/22/20 11:03 AM, Edson Richter wrote: > > > > > > > > > > > > Streaming replication. Initiated via pg_basebackup. > > > > Settings on master server: > > > > # - Sending Server(s) - > > # Set these on the master and on any standby that will send replication > > data. > > max_wal_senders = 2 # max number of walsender processes > > (change requires restart) > > wal_keep_segments = 25 # in logfile segments, 16MB each; 0 > disables > > #wal_sender_timeout = 60s # in milliseconds; 0 disables > > max_replication_slots = 2 # max number of replication > > slots (change requires restart) > > #track_commit_timestamp = off # collect timestamp of transaction > > commit (change requires restart) > > # - Master Server - > > # These settings are ignored on a standby server. > > #synchronous_standby_names = '' # standby servers that provide sync > > rep number of sync standbys and comma-separated list of > > application_name from standby(s); '*' = all > > #vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is > > delayed > > > > > > > > Settings on slave server: > > > > # - Standby Servers - > > # These settings are ignored on a master server. > > hot_standby = on# "on" allows queries during > > recovery (change requires restart) > > max_standby_archive_delay = -1 # max delay before canceling > > queries when reading WAL from archive; -1 allows indefinite delay > > max_standby_streaming_delay = -1# max delay before canceling > > queries when reading streaming WAL; -1 allows indefinite delay > > wal_receiver_status_interval = 10s # send replies at least this > > often 0 disables > > hot_standby_feedback = on # send info from standby to > > prevent query conflicts > > wal_receiver_timeout = 0# time that receiver waits for > > communication from master in milliseconds; 0 disables > > wal_retrieve_retry_interval = 5s# time to wait before retrying > > to retrieve WAL after a failed attempt > > What are the settings for: > > archive_mode > archive_command > > on the standby? > > Are the files in pg_xlog on the standby mostly from well in the past? > > > Actually, standby server is sending wals to a backup (barman) server: > > archive_mode = always # enables archiving; off, on, or always > (change requires restart) > archive_command = 'rsync -e "ssh -2 -C -p 2022" -az %p > barman@192.168.0.2:/dados/barman/dbcluster/incoming/%f' And the above is working, the files are showing up on the barman server? Yes, it is working. Last X'log file is present on all thee servers. Also, comparting last transaction number on master and slave shows that all are in sync. Last, but not least, select max(id) from a busy table shows same id (when queried almost simultaneously using a simple test routine). > > > The files are about 7 months old. Are there newer files that would indicate that the streaming is working? Yes, streaming is working properly (as stated above). Thanks, Edson Richter > > > Thanks, > > Edson > > > > > > > Regards, > > > > Edson > > > > > > > > > > > Edson > > > > > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- Adrian Klaver adrian.kla...@aklaver.com
Re: How to fix 0xC0000005 exception in Postgres 9.0
On 2/22/20 2:39 PM, Andrus wrote: Hi! I have no idea. I changed application not to send ; before select. This solves issues in both servers. So using ; as first character before select in ODBC command like ;SELECT * from mytable Causes C5 is Postgres 9.0 and ODBC client hangup with "connection reset by peer" message in log file in Postgres 9.6 It was great help and especially great ODBC log analyzing in this list, thanks. For psqlODBC issues I would suggest asking here: https://www.postgresql.org/list/pgsql-odbc/ Chances are better that there will be someone there that could answer you questions. I posted error message details issue long time ago in this pgsql-odbc list but havent got solution. I posted it as separate message here and in This is a different issue and involves a product VFP that is EOL 5-10 years depending on support package. I'm going to say the hand writing is on the wall and it is time to upgrade software. https://stackoverflow.com/questions/60357505/how-to-fix-psqlodbc-driver-regression-to-get-error-message-details Andrus. -- Adrian Klaver adrian.kla...@aklaver.com
Re: How to get error message details from libpq based psqlODBC driver (regression)
On 2/22/20 2:37 PM, Andrus wrote: Hi! I'm looking for a way to fix psqlODBC driver regression. Starting at psqlODBC 09.05.0100 when psqlODBC driver uses libpq for all operations (earlier versions used libpg only for authentication) ODBC client does not show error message details. For example, users got only generic error message like Connectivity error: ERROR: insert or update on table "mytable" violates foreign key constraint "mytable_myfield_fkey Error message details like Key (myfield)=(mykeyvalue) is not present in table "mymastertable".; are no more returned. How to detailed error message or at least only bad key value "mykeyvalue" which is returned in error message details ? Some ideas: 1. First versions of libpq based ODBC drivers returned same error message "no resource found" for all errors. Hiroshi has fixed it it later versions. Maybe psqlODBC code can fixed to restore pre-libpq behaviour. 2. Maybe analyzing odbc logs from pre and after 09.05.0100 drivers may provide solution. I can provide ODBC log files. 3. psqlODBC allows to pass parameters to libpq from connection string. Maybe some libpq parameter can fix this. 4. Maybe some Postgres query, postgres extension or direct odbc or libpq call can used to return last error message details like Windows API GetLastError() or Unix global errno. 5. Maybe it is possible to to create method which returns error message detals from postgres log file. Postgres 12.2 and latest psqlODBC driver 12.01. are used. psqlODBC is called from Visual FoxPro What does the log_error_verbosity setting in postgresql.conf show? Andrus. -- Adrian Klaver adrian.kla...@aklaver.com
Re: How to get error message details from libpq based psqlODBC driver (regression)
On 2/22/20 5:19 PM, Adrian Klaver wrote: On 2/22/20 2:37 PM, Andrus wrote: Hi! I'm looking for a way to fix psqlODBC driver regression. Starting at psqlODBC 09.05.0100 when psqlODBC driver uses libpq for all operations (earlier versions used libpg only for authentication) ODBC client does not show error message details. For example, users got only generic error message like Connectivity error: ERROR: insert or update on table "mytable" violates foreign key constraint "mytable_myfield_fkey Error message details like Key (myfield)=(mykeyvalue) is not present in table "mymastertable".; are no more returned. How to detailed error message or at least only bad key value "mykeyvalue" which is returned in error message details ? [snip] What does the log_error_verbosity setting in postgresql.conf show? Does that affect what the ODBC driver sends back to the user? -- Angular momentum makes the world go 'round.
Re: How to fix 0xC0000005 exception in Postgres 9.0
On 2/22/20 5:12 PM, Adrian Klaver wrote: On 2/22/20 2:39 PM, Andrus wrote: [snip] This is a different issue and involves a product VFP that is EOL 5-10 years depending on support package. I'm going to say the hand writing is on the wall and it is time to upgrade software. I don't know where you work, but where I work, old programs where the source code disappeared ages ago, but have worked reliably for 15+ years is distressingly common. Breaking userland backwards compatibility is a mortal sin, and one of the reasons that MS software is so popular is that they work so hard to *not* break userland backwards compatibility. -- Angular momentum makes the world go 'round.
Re: Replication: slave server has 3x size of production server?
On 2/22/20 2:51 PM, Edson Richter wrote: Yes, it is working. Last X'log file is present on all thee servers. Also, comparting last transaction number on master and slave shows that all are in sync. Last, but not least, select max(id) from a busy table shows same id (when queried almost simultaneously using a simple test routine). Well something is keeping those WAL file around. You probably should analyze your complete setup to see what else is touching those servers. > > > The files are about 7 months old. Are there newer files that would indicate that the streaming is working? Yes, streaming is working properly (as stated above). Thanks, Edson Richter -- Adrian Klaver adrian.kla...@aklaver.com
Re: How to fix 0xC0000005 exception in Postgres 9.0
> On Feb 22, 2020, at 4:28 PM, Ron wrote: > > On 2/22/20 5:12 PM, Adrian Klaver wrote: >> On 2/22/20 2:39 PM, Andrus wrote: > [snip] >> This is a different issue and involves a product VFP that is EOL 5-10 years >> depending on support package. I'm going to say the hand writing is on the >> wall and it is time to upgrade software. > > I don't know where you work, but where I work, old programs where the source > code disappeared ages ago, but have worked reliably for 15+ years is > distressingly common. > > Breaking userland backwards compatibility is a mortal sin, and one of the > reasons that MS software is so popular is that they work so hard to not break > userland backwards compatibility. > > -- Apparently at the expense of forward compatibility;) > Angular momentum makes the world go 'round.
Re: How to fix 0xC0000005 exception in Postgres 9.0
Greetings, * Ron (ronljohnso...@gmail.com) wrote: > Breaking userland backwards compatibility is a mortal sin, and one of the > reasons that MS software is so popular is that they work so hard to *not* > break userland backwards compatibility. It's also a reason why it's a mess and not everyone is terribly thrilled to have to deal with it. Changes that break things on users should be considered and weighed, and that's what the discussion here should be driving towards. Neither "never break anything ever" or "break everything every release" is tenable. Seems we've managed to do a pretty good job over time, based on what I've heard and our popularity, and I dare say it'd be best if we continued on in much the way we have these past decades. Thanks, Stephen signature.asc Description: PGP signature
Re: How to fix 0xC0000005 exception in Postgres 9.0
On 2/22/20 3:28 PM, Ron wrote: On 2/22/20 5:12 PM, Adrian Klaver wrote: On 2/22/20 2:39 PM, Andrus wrote: [snip] This is a different issue and involves a product VFP that is EOL 5-10 years depending on support package. I'm going to say the hand writing is on the wall and it is time to upgrade software. I don't know where you work, but where I work, old programs where the source code disappeared ages ago, but have worked reliably for 15+ years is distressingly common. I see the same thing. In the end it comes down to whether you want to pay the price one workaround at a time or by moving forward to something you don't have to cross your fingers everytime you start it. Breaking userland backwards compatibility is a mortal sin, and one of the reasons that MS software is so popular is that they work so hard to *not* break userland backwards compatibility. That is not true. I have a programs that have to run on old versions of Windows as they no longer run reliably or at all on newer Windows versions and the client does not want to upgrade/change the program. And no, compatibility mode is not the answer. -- Angular momentum makes the world go 'round. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Reset DB stats suggestion pg_stat_reset()
Thanks Adrian. I am aware about the functions. Here need is, can we use this?.or is there any known effects after firing the functions?. Thanks, Postgann. On Sat, Feb 22, 2020 at 10:47 PM Adrian Klaver wrote: > On 2/22/20 12:26 AM, postggen2020 s wrote: > > Hi Team, > > > > Good Afternoon, > > > > We have seen some deadlocks and tempfile count in pg_stat_database view. > > We are trying to reset the stats. > > Can we use pg_stat_reset() function to reset these stats without any > > impact stats of databases. > > Please advise the process to reset the stats. > > See here: > > https://www.postgresql.org/docs/12/monitoring-stats.html > > Table 27.20. Additional Statistics Functions > > It details what your options are. > > > > > Thanks. > > Postggen. > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
