Pgpool is crashing when terminating user session
Hello all, We are having master-slave setup with pgpool pointing, only to master server. Whenever i tried to terminate the long running session on db end using SELECT pg_terminate_backend(pid), pgpool is getting crashed. Many blogs are saying this is the expected behaviour of pgpool, but my question is there anyway to terminate unwanted sessions on db without loosing other connections. Because restarting entire system every time for a single trouble causing session is a big hectic for us. Please share you ideas on this. Thanks & Regards, Rajamohan.J
ERROR: canceling statement due to conflict with recovery
Hello all, Your expertise is needed on this. I was going through previous mails regarding the same topic and was able to setup the slave with hot_standby_feedback enabled. Queries are running fine with bloat occuring on master. I tried the below scenario, where i disabled hot_standby_feedback and on table level disabled autovacuum for 2 big tables on master. Ran the query on the slave machine but still conflict error occurs. I checked the *pg_stat_database_conflicts* view, the counter is increasing for *confl_snapshot*. Cross-checked with *pg_stat_user_tables* view, last time autovacuum happened for the 2 tables was 1 day before. My doubt, even though no autovacuum and no DML activities happening for both the tables in master. Why is conflict error occuring? Thanks & Regards, Rajamohan.J
Steps required for increasing disk size in EC2 instance with minimal downtime
Hello all, We have configured RAID 0 setup, with 8 EBS volumes of size 700G. We combined those volumes under a single mount point for the data directory. Archives are configured to be stored on different EC2 instances.Currently disk utilisation of data directory crossed 90%. We need to increase the disk with less downtime. Can anyone please share with me the steps to increase the disk size in EC2 instances with minimal downtime. xvdf202:80 0 700G 0 disk └─md127 9:127 0 5.5T 0 raid0 /opt/postgres xvdg202:96 0 700G 0 disk └─md127 9:127 0 5.5T 0 raid0 /opt/postgres xvdh202:112 0 700G 0 disk └─md127 9:127 0 5.5T 0 raid0 /opt/postgres xvdi202:128 0 700G 0 disk └─md127 9:127 0 5.5T 0 raid0 /opt/postgres xvdj202:144 0 700G 0 disk └─md127 9:127 0 5.5T 0 raid0 /opt/postgres xvdk202:160 0 700G 0 disk └─md127 9:127 0 5.5T 0 raid0 /opt/postgres xvdl202:176 0 700G 0 disk └─md127 9:127 0 5.5T 0 raid0 /opt/postgres xvdm202:192 0 700G 0 disk └─md127 9:127 0 5.5T 0 raid0 /opt/postgres /dev/md127 5.5T 5.2T 306G 95% /opt/postgres Thanks & Regards, Rajamohan.J
Need to place pgpool logs on separate directory
Hello all, I want to place pgpool logs on a separate directory rather than in a syslog file. So I changed the below parameters, but still logging information are being written to the syslog file. Any idea or suggestion why this behaviour? *pgpool-II version 4.1.4 (karasukiboshi)* Tried all the below combinations, none of them works 1. log_destination = 'stderr' syslog_facility = 'LOCAL0' syslog_ident = 'pgpool' logdir = '/data/pgpool' 2. log_destination = 'stderr' #syslog_facility = 'LOCAL0' #syslog_ident = 'pgpool' logdir = '/data/pgpool' 3. log_destination = 'syslog' syslog_facility = 'LOCAL0' syslog_ident = 'pgpool' logdir = '/data/pgpool' Thanks & Regards, Rajamohan.J
Re: Need to place pgpool logs on separate directory
Thanks Tatsuo for the info. I will contact pgpool forum regarding this. Also I tried restarting and reloading the pgpool service after each of the above parameter changes, still it's not working. item | value | description --++- log_destination | stderr | logging destination logdir | /data/pgpool | PgPool status file logging directory syslog_facility | LOCAL0 | syslog local faclity syslog_ident | pgpool | syslog program ident string Thanks & Regards, Rajamohan.J On Thu, Nov 12, 2020 at 9:41 AM Tatsuo Ishii wrote: > > Hello all, > > > > I want to place pgpool logs on a separate directory rather than in a > syslog > > file. So I changed the below parameters, but still logging information > are > > being written to the syslog file. Any idea or suggestion why this > > behaviour? > > > > > > > > *pgpool-II version 4.1.4 (karasukiboshi)* > > > > Tried all the below combinations, none of them works > > > > 1. > > log_destination = 'stderr' > > syslog_facility = 'LOCAL0' > > syslog_ident = 'pgpool' > > logdir = '/data/pgpool' > > > > 2. > > log_destination = 'stderr' > > #syslog_facility = 'LOCAL0' > > #syslog_ident = 'pgpool' > > logdir = '/data/pgpool' > > > > 3. > > log_destination = 'syslog' > > syslog_facility = 'LOCAL0' > > syslog_ident = 'pgpool' > > logdir = '/data/pgpool' > > 1 or 2 should work. You might want to check whether 'stderr' is > actually set to log_destination parameter by using psql: > > pgpool show log_destination; > > If it's set but still logs are sent to syslog, try reloading or > restarting pgpool. > > By the way this is not the most appropriate forum to ask questions > regarding pgpool. Please use pgpool-general mailing list instead: > > https://www.pgpool.net/mailman/listinfo/pgpool-general > > Best regards, > -- > Tatsuo Ishii > SRA OSS, Inc. Japan > English: http://www.sraoss.co.jp/index_en.php > Japanese:http://www.sraoss.co.jp >
Prevent users from executing pg_dump against tables
Hello all, In our production db infrastructure, we have one read_only role which has read privileges against all tables in schema A. We are planning to grant this role to some developers for viewing the data, but also I want to limit the users from executing statements like copy or using pg_dump. Main reason being I don't want the data to be copied from the database to their local machines. I tried by implementing triggers, but was not able to figure out a way to restrict the pg_dump and allow only select statements. Postgresql version - 12 Ec2 based postgres database Is there a way to implement this? Please advise. Thanks & Regards, Rajamohan.J Devops Cloud Architect Email:garajamo...@gmail.com
Null database entry in pg_stat_database on pg-12 version
Hello all, Today I came across this null database entry with oid as 0 in *pg_stat_database* on *postgresql-12* version. Any particular use case is there for this? It also has some column values, how it is gathering those data and which database is responsible for this? I am not seeing this in the postgresql-10 version. Can someone clarify this? postgres=# select * from pg_catalog.pg_stat_database where datid='0'; -[ RECORD 1 ]-+-- datid| 0 datname | numbackends | 0 xact_commit| 0 xact_rollback | 0 blks_read | 14253 blks_hit | 1753072 tup_returned | 271673 tup_fetched | 212113 tup_inserted | 152603 tup_updated | 478 tup_deleted | 14 conflicts | 0 temp_files | 0 temp_bytes| 0 deadlocks | 0 checksum_failures | checksum_last_failure | blk_read_time | 0 blk_write_time | 0 stats_reset| 2021-05-24 08:36:09.580433+00 Thanks & Regards, Rajamohan.J Mobile : +91 8098167651, +91 7259157485
Symbolic link breaks for postgresql.auto.conf
Hello all, Can someone explain how *postgresql.auto.conf *file contents gets updated, when running an alter system statement followed by pg_reload_conf? I am trying to keep a symbolic link for the *postgresql.auto.conf* outside the data directory. The link gets created but whenever I issue an alter system statement it gets broken. The main reason for this symbolic link creation is to retain the parameter values associated with this cluster, because we will be copying the entire data directory from another server to this server from time to time for testing purposes. And no issues with other files such as hba and postgresql.conf. *Operating System: Ubuntu 18.04.1 LTS* *Postgresql : 12.7* Step1: Created symbolic links and i am inside data directory lrwxrwxrwx 1 postgres postgres 41 Jun 3 05:12 postgresql.conf -> /etc/postgresql/12/test1/postgresql.conf lrwxrwxrwx 1 postgres postgres 37 Jun 3 05:12 pg_hba.conf -> /etc/postgresql/12/test1/pg_hba.conf lrwxrwxrwx 1 postgres postgres 46 Jun 6 15:19 postgresql.auto.conf -> /etc/postgresql/12/test1/postgresql.auto.conf Step2: alter system set checkpoint_timeout='2min';select pg_reload_conf(); ALTER SYSTEM pg_reload_conf t (1 row) Step3: Symbolic link got broken lrwxrwxrwx 1 postgres postgres 41 Jun 3 05:12 postgresql.conf -> /etc/postgresql/12/test1/postgresql.conf lrwxrwxrwx 1 postgres postgres 37 Jun 3 05:12 pg_hba.conf -> /etc/postgresql/12/test1/pg_hba.conf lrwxrwxrwx 1 postgres postgres 46 Jun 6 15:19 postgresql.auto.conf Thanks & Regards, Rajamohan.J
Re: Symbolic link breaks for postgresql.auto.conf
Thanks guys. Able to resolve the issue, by using the include_if_exists parameter as Adrian suggested. Thanks & Regards, Rajamohan.J On Mon, Jun 7, 2021 at 1:02 AM Adrian Klaver wrote: > On 6/6/21 10:50 AM, Vijaykumar Jain wrote: > > Please reply to list also. > Ccing list > > Also please use inline or bottom posting, it makes things easier to follow. > > > Yeah I am aware of that :) > > > > I was referring to if the main config is managed via some config > > management tool like puppet, ansible or anything else that would wipe > > the changes made temporarily. > > Which 'includes' could handle depending on what the config management > tool is using as the starting main config(postgresql.conf I presume). If > that file is seeded with an include, or better yet include_if_exists, > that point at files outside PG_DATA then you would have the ability to > override settings at will with a reload/restart. > > If the above does not cover your use case you will need to provide a > more detailed description of how your configuration management is done. > > > > > > > On Sun, Jun 6, 2021, 10:45 PM Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> wrote: > > > > On 6/6/21 10:02 AM, Vijaykumar Jain wrote: > > > Yes, I learnt it the hard way as well :) I made changes via the > > alter > > > system, and did a pg_restore, and the changes were lost :) > > > > > > use the alter system for ad hoc changes, but make sure those > > changes are > > > added back to the postgresql.conf file immediately (or however > > the main > > > conf file is managed). > > > > I would suggest taking a look at 'includes': > > > > > https://www.postgresql.org/docs/13/config-setting.html#CONFIG-INCLUDES > > < > https://www.postgresql.org/docs/13/config-setting.html#CONFIG-INCLUDES> > > > > > > > > > > > > > On Sun, 6 Jun 2021 at 22:17, Tom Lane > <mailto:t...@sss.pgh.pa.us> > > > <mailto:t...@sss.pgh.pa.us <mailto:t...@sss.pgh.pa.us>>> wrote: > > > > > > RAJAMOHAN > <mailto:garajamo...@gmail.com> <mailto:garajamo...@gmail.com > > <mailto:garajamo...@gmail.com>>> writes: > > > > I am trying to keep a symbolic link for the > > > *postgresql.auto.conf* outside > > > > the data directory. The link gets created but whenever I > > issue an > > > alter > > > > system statement it gets broken. > > > > > > This is not supported. Don't do it. > > > > > > The right way to keep the hand-maintained config files > > outside the > > > data directory is not the way you've done it here, either. > > It might > > > accidentally work, but the preferred way is to put > > "data_directory = > > > whatever" in postgresql.conf and then start the postmaster > > with -D > > > pointing at where the config files are. > > > > > > But in any case, postgresql.auto.conf is not hand-maintained; > it > > > is part of the cluster data, so it belongs in the data > directory. > > > > > > regards, tom lane > > > > > > > > > > > > > > > -- > > > Thanks, > > > Vijay > > > Mumbai, India > > > > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > >
