Re: [GENERAL] pg_basebackup running from a remote machine
Does pg_basebackup on a remote machine follow the standard libpq protocol. I am not able to force it to use ssl, despite having an entry in pg_hba.conf: hostnossl all all all reject >From the same remote machine, psql is forced to use ssl. Makes me wonder whether pg_basebackup has a different protocol. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_basebackup running from a remote machine
If pg_basebackup is run from a remote machine with compress option --gzip , compress level 9, will the compression occur prior to the data being sent on the network or after it has been received at the remote machine. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgaduit - is there a way to audit a role
Is there a way to audit a group like as follows alter role db_rw set pgaudit.log = 'read,write,function,ddl' and then any user part of db_rw role can be audited automatically. It does not seem to work if I connect to the db as rakesh who is part of db_rw role. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_audit to mask literal sql
By mask I mean pgaudit should log where ssn = '123-456-7891' as where ssn = '?' -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_audit to mask literal sql
Yes all who interact with HIPAA data are trained for HIPAA SOP. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_audit to mask literal sql
No they do select. It is fine in HIPAA to view data which are protected, if it is part of your job. What is not fine is being careless with that protected data and let unauthorized person view that data. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_audit to mask literal sql
Is there a way in pgaudit to mask literal sqls like the below: insert into table (col1,col2) values(1,2) select * from table where col1 = 1 These sqls are typed by our QA folks using pgadmin. pgaudit records this verbatim which runs afoul of our HIPAA requirement. Prepared statements are not an issue since pgaudit provides a way to suppress values. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] starting PG command line options vs postgresql.con
I am new to Docker env and I see that PG, as a container is started with parameters like this: docker run -it \ --detach \ --name name \ --restart=unless-stopped \ -p 5432:5432 \ -e PGDATA=/var/lib/postgresql/data/pg10 -N 500 \ -B 3GB \ -S 6291kB \ -c listen_addresses=* \ -c effective_cache_size=9GB \ -c maintenance_work_mem=768MB \ -c min_wal_size=2GB \ -c max_wal_size=4GB \ I would prefer using postgresql.conf. what is the consensus in this forum regarding command line vs postgresql.conf. Also if conflicting, which one takes priority. thanks -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] controlling users to audit in pgaudit
Hi I want to find an easy way to control who not to audit. Let us say we have 50 users out of which we don't want to monitor only 5 users. Is there a way to set logging rules to include all except those 5. alter system set pgaudit.log will include all. From here how do I exclude 5 users. thanks -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to get login user name and host name in pgaudit
I installed latest pgaudit (1.2) with pg10. I am testing it and I see that it does not log the login user name and host name. For example, if user mary is running select * from sensitive_table, I want Mary and the machine from where she ran in the log. It seems to log the ids which needs to be joined with pg_ views to convert it into login user name and host name. any pointers on how to get it done. thanks. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to find out extension directory
I am documenting on automating installation of pgaudit extension for containers. On my laptop I see that the directory where the files pgaudit.control and pgaudit--1.2.sql needs to be present is /usr/share/postgresql/10/extension. How do I know beforehand where the dir path is ? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is it OK to create a directory in PGDATA dir
Hey I am not the container guy. I agree with you 100%. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is it OK to create a directory in PGDATA dir
In the container world, sometime the only persistent storage path (that is, storage outside container world) is PGDATA. Is it fine to create a subdir inside PGDATA and store our stuff there, or will PG freak out seeing a foreign object. thanks -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Force SSL connection
In PG 9.6 or PG 10, is there a way to force only SSL based connections coming from pgadmin or dbeaver. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Revolut - postgres ?
https://blog.revolut.com/no-excuses-we-let-you-down-32f81e64f974 The career section of the company's web page lists PG as one of the tech stack. Would be interesting to know the details. "At around 07:00 BST on Friday morning, our transaction database began to malfunction. Naturally, we followed procedure and switched to a backup server. Unfortunately, the backup server began to drastically slow down and was struggling to process live transactions. To make matters worse, we had an unexpected spike of 50% in user activity on Friday and the backup server could not handle such a large increase in volume." -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG and database encryption
Thanks John and JD. John: Are you telling that the backup of a database has no protection? -- View this message in context: http://www.postgresql-archive.org/PG-and-database-encryption-tp5979618p5979624.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG and database encryption
We have a requirement to encrypt the entire database. What is the best tool to accomplish this. Our primary goal is that it should be transparent to the application, with no change in the application, as compared to un-encrypted database. Reading about pgcrypto module, it seems it is good for few columns only and using it to encrypt entire database is not a good use-case. Is this which can be done best by file level encryption? What are the good tools on Linux (RHES), preferably open-source. Thanks -- View this message in context: http://www.postgresql-archive.org/PG-and-database-encryption-tp5979618.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Developer GUI tools for PostgreSQL
You can try DBeaver. It is a generic GUI tool which works with practically all RDBMS. It is java based, and I find it bit slow. However judging by the frequent updates I get, it seems to be very active. -- View this message in context: http://www.postgresql-archive.org/Developer-GUI-tools-for-PostgreSQL-tp5972993p5973305.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incremental / Level -1 backup in PG
>note postgres' WAL archive is by block, not by transaction. My understanding is that only the first time a block is updated after a checkpoint, is the entire block is written to the WAL logs. And for that full_page_writes has to be set to ON. The only other time PG writes entire block to the WAL is during the time of backup regardless of full_page_writes setting. AFAIK rest of the time, WAL takes only row changes. Otherwise PG will be generating large number of WAL logs. I hope I am right :-) > also note that postgres effectively does copy-on-write, since update's are > treated >as insert+delete, so the same blocks aren't written over and over nearly >as much as they might be in the oracle storage model. Good point. -- View this message in context: http://www.postgresql-archive.org/Incremental-Level-1-backup-in-PG-tp5951072p5951343.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incremental / Level -1 backup in PG
Greetings, >The short answer is 'no'. There are complications around this, >particularly at the edges and because files can be written and rewritten >as you're reading them. >Basically, no file with a timestamp after the >checkpoint before the backup can be omitted from an incremental backup. what you have written above applies to oracle/db2 too. In case you are not aware, during backup, those products have a mechanism to save the image of any changing block as it existed before the start of the backup. that is used to reconstruct the PIT image of the block. Anyhow looks like this can't be done in PG. thanks all. -- View this message in context: http://www.postgresql-archive.org/Incremental-Level-1-backup-in-PG-tp5951072p5951148.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incremental / Level -1 backup in PG
basebackup + WAL archive lets you do just exactly this. you can restore to any transaction between when that basebackup was taken, and the latest entry in the WAL archive, its referred in the documentation as PITR, Point in Time Recovery. Yes John I do know about using WAL archive. IMO that will not be as fast as restoring using the incremental backup. Eg: It is common to take a full backup on weekends and incremental on weeknights. If we have to restore upto Thu afternoon, which one do you think will be faster :- 1 - Restore from basebackup. 2 - Restore from wed night backup 3 - Apply WAL logs after wed night backup until the time we want to restore. vs 1 - Restore from basebackup 2 - Apply WAL logs from weekend until the time we want to restore. If first choice is lot faster in Oracle,DB2, I have reasons to believe that the same should be true for PG also. But as someone explained, the PG technology can not support this. Anyhow it was an academical question. -- View this message in context: http://www.postgresql-archive.org/Incremental-Level-1-backup-in-PG-tp5951072p5951147.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Determining replication lag
I found the following SQL in stackoverflow: SELECT pg_last_xlog_receive_location() receive, pg_last_xlog_replay_location() replay, ( extract(epoch FROM now()) - extract(epoch FROM pg_last_xact_replay_timestamp()) )::int lag I get different result in primary and slave. On primary the col lag always shows some number, usually more than 3000, but on slave it is always zero. Why the discrepancy ? -- View this message in context: http://postgresql.nabble.com/Determining-replication-lag-tp5933003.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgbench and scaling
"Are the TPS numbers per pgbench? If so, then you're getting 10x490=4900 TPS system wide, or 20*280=5600 TPS system wide. " Per pgbench. Your explanation makes sense. thanks. -- View this message in context: http://postgresql.nabble.com/pgbench-and-scaling-tp5930891p5931131.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
