install pgcrypto module to existing postgreSQL
hello experts, i have Installed and created postgres 11.2 and created couple of database. Now i got new requirement to encrypt data on few tables. to perform this application team is asking us to install pgcrypto module on postgres cluster. what is the way to install pgcrypt library ? please advise -- *Regards,#! Pavan Kumar--*- *Sr. Database Administrator..!* *NEXT GENERATION PROFESSIONALS, LLC* *Cell# 267-799-3182 # pavan.dba27 (Gtalk) * *India # 9000459083* *Take Risks; if you win, you will be very happy. If you lose you will be Wise *
Re: install pgcrypto module to existing postgreSQL
Hello Adrian, i have used configure command install postgres $ ./configure --prefix=/pgbin/11.2 --with-segsize=32 --with-pgport=5432 once configure is done, used make and make install to install postgres. On Tue, Sep 17, 2019 at 4:06 PM Adrian Klaver wrote: > On 9/17/19 1:25 PM, Pavan Kumar wrote: > > hello experts, > > > > i have Installed and created postgres 11.2 and created couple of > > How did you install? > > > database. Now i got new requirement to encrypt data on few tables. to > > perform this application team is asking us to install pgcrypto module on > > postgres cluster. > > what is the way to install pgcrypt library ? > > > > please advise > > > > -- > > *Regards, > > > > #! Pavan Kumar > > --*- > > *Sr. Database Administrator..!* > > *NEXT GENERATION PROFESSIONALS, LLC* > > *Cell# 267-799-3182 # pavan.dba27 (Gtalk) * > > *India # 9000459083* > > > > *Take Risks; if you win, you will be very happy. If you lose you > > will be Wise * > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- *Regards,#! Pavan Kumar--*- *Sr. Database Administrator..!* *NEXT GENERATION PROFESSIONALS, LLC* *Cell# 267-799-3182 # pavan.dba27 (Gtalk) * *India # 9000459083* *Take Risks; if you win, you will be very happy. If you lose you will be Wise *
Re: install pgcrypto module to existing postgreSQL
Ron, yes, Installed with source code. here is my postgres configuration BINDIR = /pgbin/11.2/bin DOCDIR = /pgbin/11.2/share/doc/postgresql HTMLDIR = /pgbin/11.2/share/doc/postgresql INCLUDEDIR = /pgbin/11.2/include PKGINCLUDEDIR = /pgbin/11.2/include/postgresql INCLUDEDIR-SERVER = /pgbin/11.2/include/postgresql/server LIBDIR = /pgbin/11.2/lib PKGLIBDIR = /pgbin/11.2/lib/postgresql LOCALEDIR = /pgbin/11.2/share/locale MANDIR = /pgbin/11.2/share/man SHAREDIR = /pgbin/11.2/share/postgresql SYSCONFDIR = /pgbin/11.2/etc/postgresql PGXS = /pgbin/11.2/lib/postgresql/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--prefix=/pgbin/11.2' '--with-segsize=32' '--with-pgport=5432' CC = gcc CPPFLAGS = -D_GNU_SOURCE CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 CFLAGS_SL = -fPIC LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/pgbin/11.2/lib',--enable-new-dtags LDFLAGS_EX = LDFLAGS_SL = LIBS = -lpgcommon -lpgport -lpthread -lz -lreadline -lrt -lcrypt -ldl -lm VERSION = PostgreSQL 11.2 On Tue, Sep 17, 2019 at 4:22 PM Ron wrote: > IOW, you installed it from source instead of a package. > > On 9/17/19 4:18 PM, Pavan Kumar wrote: > > Hello Adrian, > > i have used configure command install postgres > > $ ./configure --prefix=/pgbin/11.2 --with-segsize=32 --with-pgport=5432 > > once configure is done, used make and make install to install postgres. > > > > On Tue, Sep 17, 2019 at 4:06 PM Adrian Klaver > wrote: > >> On 9/17/19 1:25 PM, Pavan Kumar wrote: >> > hello experts, >> > >> > i have Installed and created postgres 11.2 and created couple of >> >> How did you install? >> >> > database. Now i got new requirement to encrypt data on few tables. >> to >> > perform this application team is asking us to install pgcrypto module >> on >> > postgres cluster. >> > what is the way to install pgcrypt library ? >> > >> > please advise >> > > -- > Angular momentum makes the world go 'round. > -- *Regards,#! Pavan Kumar--*- *Sr. Database Administrator..!* *NEXT GENERATION PROFESSIONALS, LLC* *Cell# 267-799-3182 # pavan.dba27 (Gtalk) * *India # 9000459083* *Take Risks; if you win, you will be very happy. If you lose you will be Wise *
scram-sha-256 encrypted password in pgpass
Hello expertes, scram-sha-256 encrypted passwords are supported in .pgpass file ? If yes kindly provide us an example. I am using below format and it is not working for me *pglnx1*:*5432*:pgbouncer:*pgadmin*:"SCRAM-SHA-256$4096:6IDsjfedwsdpymp0Za7jaMew==$rzSoYL4ZYsW1WJAj7Lt3JtNLNR73AVY7sfsauikweblk][=:Hxx/juPXJZHy5djPctI=*"* Please advise -- *Regards,#! Pavan Kumar--*- *Sr. Database Administrator..!* *NEXT GENERATION PROFESSIONALS, LLC* *Cell# 267-799-3182 # pavan.dba27 (Gtalk) * *India # 9000459083* *Take Risks; if you win, you will be very happy. If you lose you will be Wise *
Re: scram-sha-256 encrypted password in pgpass
Adrian, David, Thank you so much for the quick response. What would be the point of storing the encrypted password instead of the plaintext one? As per our organization security policies, we can 't keep any passwords in plain text format. I am working on postgres + pgbouncer setup, tested pgbouncer 1.14 where we have support to use encrypted password in userlist,txt file. I am surprised why pgpass is not supporting encrypted passwords. On Mon, Jun 22, 2020 at 5:04 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > Please don't cross-post. > > On Mon, Jun 22, 2020 at 1:35 PM Pavan Kumar wrote: > >> scram-sha-256 encrypted passwords are supported in .pgpass file ? If yes >> kindly provide us an example. >> >> I am using below format and it is not working for me >> >> *pglnx1*:*5432*:pgbouncer:*pgadmin*:"SCRAM-SHA-256$4096:6IDsjfedwsdpymp0Za7jaMew==$rzSoYL4ZYsW1WJAj7Lt3JtNLNR73AVY7sfsauikweblk][=:Hxx/juPXJZHy5djPctI=*"* >> >> The documentation doesn't say so one way or the other so I would go with > no. The password in the pgpass file has to be the plaintext password. The > client, upon speaking with the server, will decide whether to send the > plaintext password to the server or encrypt it prior to transmission. > > What would be the point of storing the encrypted password instead of the > plaintext one? > > David J. > > -- *Regards,#! Pavan Kumar--*- *Sr. Database Administrator..!* *NEXT GENERATION PROFESSIONALS, LLC* *Cell# 267-799-3182 # pavan.dba27 (Gtalk) * *India # 9000459083* *Take Risks; if you win, you will be very happy. If you lose you will be Wise *
postgres database complete recovery
annot stat ‘/pgdata/archive/5444/0002.history’: No such file or directory >>>*** error 2019-04-04 06:30:40.721 CDT [16044] LOG: selected new timeline ID: 2 2019-04-04 06:30:40.792 CDT [16044] LOG: archive recovery complete cp: cannot stat ‘/pgdata/archive/5444/0001.history’: No such file or directory 2019-04-04 06:30:40.906 CDT [16043] LOG: database system is ready to accept connections >>>> *** opened database = >>> let's validate [postgres@pgsql-lnx1 datadir_clone]$ psql -p 5444 psql (10.6) Type "help" for help. postgres=# \l List of databases Name| Owner | Encoding | Collate |Ctype| Access privileges ---+---+--+-+-+-- fal1dbd | fal1admin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | fal1admin=CTc/fal1admin + | | | | | =T/fal1admin + | | | | | visa_system_role=c/fal1admin fal2dbd | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres + | | | | | =T/postgres + | | | | | visa_system_role=c/postgres fal3dbd | fal3admin | UTF8 | en_US.UTF-8 | en_US.UTF-8 | fal3admin=CTc/fal3admin + | | | | | =T/fal3admin ltuser| ltuser| UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres + | | | | | =T/postgres + | | | | | visa_system_role=c/postgres template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres + | | | | | =c/postgres (7 rows) postgres=# \c ltuser ltuser You are now connected to database "ltuser" as user "ltuser". ltuser=> \dt List of relations Schema |Name| Type | Owner ++---+ public | customer | table | ltuser public | district | table | ltuser public | history| table | ltuser public | item | table | ltuser public | new_order | table | ltuser public | order_line | table | ltuser public | orders | table | ltuser public | stock | table | ltuser public | warehouse | table | ltuser (9 rows) ltuser=> ltuser=> exit = >>>> validation results since we lost every thing, it only performed recovery till last available log in hotbackup. since we lost every thing, it only performed recovery till last available log in hotbackup. latest data in current wal log is lost and there we have test table. lost of latest wal file/log will give some data loss, need to find a way to have redundant copy on latest wall logfile -- *Regards,#! Pavan Kumar--*- *Sr. Database Administrator..!* *NEXT GENERATION PROFESSIONALS, LLC* *Cell# 267-799-3182 # pavan.dba27 (Gtalk) * *India # 9000459083* *Take Risks; if you win, you will be very happy. If you lose you will be Wise *
configure multiple repository path in pgbackrest
Hello Experts, Is it possible to configure multiple backup repositories in pgbackrest tool in one server? I am getting few issues with that. -- *Regards,#! Pavan Kumar--*- *Sr. Database Administrator..!* *NEXT GENERATION PROFESSIONALS, LLC* *Cell# 267-799-3182 # pavan.dba27 (Gtalk) * *India # 9000459083* *Take Risks; if you win, you will be very happy. If you lose you will be Wise *
Re: configure multiple repository path in pgbackrest
Hello Ron, Thank you so much for quick response. here is my configuration. [postgres@oralnx v2demo4448]$cat /etc/pgbackrest/pgbackrest.conf [v1demo] pg1-path=/oradbaudit/pg_data_dir pg1-port=5400 [v2demo] pg1-path=/oratrace/11_2_data pg1-port=4448 # Backup repository configuration [global] repo1-path=/orabackup/WORM/PGBACKUP/visdemo5400 repo1-retention-full=2 repo2-path=/orabackup/WORM/PGBACKUP/v2demo4448 repo2-retention-full=2 [postgres@oralnx v2demo4448]$ [postgres@oralnx v2demo4448]$ pgbackrest --stanza=v2demo --log-path=/oratrace/pg_alert_log --repo-path=/orabackup/WORM/PGBACKUP/v2demo4448 --log-level-console=detail --log-level-file=detail stanza-create WARN: configuration file contains invalid option 'repo2-path' >>>>>>>> * WARN: configuration file contains invalid option 'repo2-retention-full' >>>>>> * 2019-06-05 19:35:05.351 P00 INFO: stanza-create command begin 2.14: --log-level-console=detail --log-level-file=detail --log-path=/oratrace/pg_alert_log --pg1-path=/oratrace/11_2_data --pg1-port=4448 --repo1-path=/orabackup/WORM/PGBACKUP/v2demo4448 --stanza=v2demo >>>>> * for some reason it took repo1- path 2019-06-05 19:35:06.674 P00 INFO: stanza-create command end: completed successfully (1324ms) [postgres@oralnx v2demo4448]$ It got errored out when I check stanza [postgres@oralnx v2demo4448]$pgbackrest --stanza=v2demo --log-level-console=info check WARN: configuration file contains invalid option 'repo2-path' WARN: configuration file contains invalid option 'repo2-retention-full' 2019-06-05 19:36:31.188 P00 INFO: check command begin 2.14: --log-level-console=info --pg1-path=/oratrace/11_2_data --pg1-port=4448 --repo1-path=/orabackup/WORM/PGBACKUP/visdemo5400 --stanza=v2demo >>>> *** why it is not taking repo2-path ERROR [055]: : unable to open /orabackup/WORM/PGBACKUP/visdemo5400/backup/v2demo/backup.info or /orabackup/WORM/PGBACKUP/visdemo5400/backup/v2demo/backup.info.copy ERROR [055]: : /orabackup/WORM/PGBACKUP/visdemo5400/backup/v2demo/ backup.info does not exist and is required to perform a backup. HINT: has a stanza-create been performed? ERROR [055]: : /orabackup/WORM/PGBACKUP/visdemo5400/backup/v2demo/ backup.info does not exist and is required to perform a backup. HINT: has a stanza-create been performed? 2019-06-05 19:36:32.826 P00 INFO: check command end: aborted with exception [055] [postgres@oralnx v2demo4448]$ here is my configuration On Wed, Jun 5, 2019 at 10:09 AM Ron wrote: > > > On 6/5/19 9:48 AM, Pavan Kumar wrote: > > Hello Experts, > > Is it possible to configure multiple backup repositories in pgbackrest > tool in one server? > I am getting few issues with that. > > > https://pgbackrest.org/command.html#command-backup > > 3.4.9 > Repository Path Option (--repo-path) > Path where backups and archive are stored. > The repository is where pgBackRest stores backups and archives WAL > segments. > > It may be difficult to estimate in advance how much space you'll need. The > best thing to do is take some backups then record the size of different > types of backups (full/incr/diff) and measure the amount of WAL generated > per day. This will give you a general idea of how much space you'll need, > though of course requirements will likely change over time as your database > evolves. > > default: /var/lib/pgbackrest > example: --repo1-path=/backup/db/backrest > > > Since there's a --repo*1*-path, then presumably there can also be a --repo > 2-path. > > You'd presumably associate a database with repo2 by using --pg2-path > > https://pgbackrest.org/command.html#command-archive-get > > 2.5 > Stanza Options > 2.5.1 > PostgreSQL Path Option (--pg-path) > PostgreSQL data directory. > This should be the same as the data_directory setting in postgresql.conf. > Even though this value can be read from postgresql.conf or PostgreSQL it > is prudent to set it in case those resources are not available during a > restore or offline backup scenario. > > The pg-path option is tested against the value reported by PostgreSQL on > every online backup so it should always be current. > > example: --pg1-path=/data/db > > Deprecated Name: db-path > > Of course, this might not work, so you'll have to try it yourself. > > -- > Angular momentum makes the world go 'round. > -- *Regards,#! Pavan Kumar--*- *Sr. Database Administrator..!* *NEXT GENERATION PROFESSIONALS, LLC* *Cell# 267-799-3182 # pavan.dba27 (Gtalk) * *India # 9000459083* *Take Risks; if you win, you will be very happy. If you lose you will be Wise *
how to understand checkpoint information in pg_control data
Hello Experts, I have couple of questions on pg_controldata output. postgres=# \! pg_controldata $PGDATA|grep checkpoint *Latest checkpoint location: 2/9D029BB0, Latest checkpoint's REDO location:2/97002D60Latest checkpoint's REDO WAL file: 000300020097* Latest checkpoint's TimeLineID: 3 Latest checkpoint's PrevTimeLineID: 3 Latest checkpoint's full_page_writes: on Latest checkpoint's NextXID: 0:1732072 Latest checkpoint's NextOID: 40985 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Latest checkpoint's oldestXID:561 Latest checkpoint's oldestXID's DB: 1 Latest checkpoint's oldestActiveXID: 1731942 Latest checkpoint's oldestMultiXid: 1 Latest checkpoint's oldestMulti's DB: 1 Latest checkpoint's oldestCommitTsXid:0 Latest checkpoint's newestCommitTsXid:0 Time of latest checkpoint:Fri 28 Jun 2019 02:59:24 PM GMT What is "Latest checkpoint location" what is "Latest checkpoint's REDO location". to my understanding, it is records lsn number. correct me if I am wrong? Latest checkpoint's REDO WAL file:000300020097, to my understanding this wal file has the latest record lsn , from where recovery will start and rollover. please correct me if I am wrong ? -- *Regards,#! Pavan Kumar--*- *Sr. Database Administrator..!* *NEXT GENERATION PROFESSIONALS, LLC* *Cell# 267-799-3182 # pavan.dba27 (Gtalk) * *India # 9000459083* *Take Risks; if you win, you will be very happy. If you lose you will be Wise *
CPU is 100% azure rds postgreSQL-11
Hi Team, One of our RDS postgresql -11 version CPU is drastically high continuously. we are unable to find what is causing. the server is 4Vcore 100 GB storage. is there any way what is causing the issue.? we have datadog integration with custom metrics. we have done all vacuum and analyze operations, couple of restarts. as well. any Help is appreciated. thanks Pavan
