About vipulshukla

vipulshukla

Postgres Plus Advanced Server supports dynamic tuning of the database server to make the optimal usage of the system resources available on the host machine on which it is installed. The two parameters that control this functionality are located in the postgresql.conf file. These are: edb_dynatune: edb_dynatune determines how much of the host system's resources are to be used by the database server based upon the host machine's total available resources and the intended usage of the host machine. edb_dynatune_profile: The edb_dynatune_profile parameter is used to control tuning aspects based upon the expected workload profile on the database server. This parameter takes effect upon startup of the database server. Possible values are - oltp, reporting, mixed. Following are the parameters majorly used by Dynatune. > max_connections > work_mem > maintenance_work_mem > shared_buffers > max_fsm_relations > max_fsm_pages > wal_buffers > effective_cache_size > random_page_cost > checkpoint_segments > autovacuum_vacuum_threshold > autovacuum_vacuum_scale_factor > autovacuum_analyze_threshold > autovacuum_analyze_scale_factor > autovacuum_naptime > stats_row_level > autovacuum Dynatune takes the following input: 1. System Shared Memorysys_shared_mem; 2. Current allocated Shared Buffer 3. Current MaxFSMRelations settings; 4. Number of Buffers; 5. Total Memory. And sets the following category of parameters: 1. tune_max_connections 2. tune_work_mem; (Dynamically) 3. tune_maintenance_work_mem; (Dynamically) 4. tune_max_fsm_relations();—Require 5. tune_max_fsm_pages(); 6. wal_buffers; 7. tune_shared_buffers(); Shared Buffer 8. tune_effective_cache_size (Dynamically) 9. random_page_cost; (Cost Based Parameters) 10.tune_checkpoint_segments; (WAL Parameters) 11.tune_autovacuum(percentage); (Autovacuum Parameters) By default, autovacuum is enabled in Advanced Server and Dynatune is responsible for tuning the autovacuum parameters. Dynatune does not enable autovacuum. Refer the document here for detailed information on EDB Dynatune.

vipulshukla

Data redaction is a technique that limits sensitive data exposure by dynamically changing data as it is displayed for certain users. Data redaction is implemented by defining a function for each field to which redaction is to be applied. The function returns the value that should be displayed to the users subject to the data redaction. The edb_data_redaction parameter in the postgresql.conf file then determines whether or not data redaction is to be applied. By default, the parameter is enabled so the redaction policy is in effect and the following occurs: Superusers and the table owner bypass data redaction and see the original data. All other users get the redaction policy applied and see the reformatted data. If the parameter is disabled by having it set to FALSE during thesession, then the following occurs: Superusers and thetable owner bypass data redaction and see the original data. All other users get will get an error. A redaction policy can be added, altered, disabled, enabled or dropped any time. For details on this, you can refer the document on this link. I have produced a simple test case which could be referred for details on how to create a redaction policy and apply for a column like card string. Test Case: >> Login Using the superuser, or table owner and check the table where you need to apply the redaction policy. postgres=# \c postgres postgres You are now connected to database "postgres" as user "postgres". postgres=# select * from payment_details_tab; customer_id | card_string -------------+--------------------- 4000 | 1234-1234-1234-1234 4001 | 2345-2345-2345-2345 (2 rows) >> Add new policy to partially hide the card details. postgres=# BEGIN postgres=# DBMS_REDACT.add_policy( postgres=# object_schema=> 'public', postgres=# object_name=> 'payment_details_tab', postgres=# policy_name=> 'redactPolicy_001', postgres=# policy_description=> 'redactPolicy_001 for payment_details_tab table', postgres=# column_name=> 'card_string', postgres=# function_type=> DBMS_REDACT.partial, postgres=# function_parameters=> DBMS_REDACT.REDACT_CCN16_F12, postgres=# expression=> '1=1', postgres=# enable=> TRUE postgres=# ); postgres=# END; EDB-SPL Procedure successfully completed >> Check the table data again to see if the superuser is able to see the data. postgres=# select * from payment_details_tab; customer_id | card_string -------------+--------------------- 4000 | 1234-1234-1234-1234 4001 | 2345-2345-2345-2345 (2 rows) >> Connect to a different user who is neither a superuser nor table owner and check the table data. postgres=# \c postgres vipul You are now connected to database "postgres" as user "vipul". postgres=> select * from payment_details_tab; customer_id | card_string -------------+--------------------- 4000 | ****-****-****-1234 4001 | ****-****-****-2345 (2 rows)

vipulshukla

Hi Kishor, For a server with internet and EDB repo configured, you could just use 'yum install edb-pgpool37', which will automatically resolve the required dependencies. [root@localhost data_gathering]# yum install edb-pgpool37 Loaded plugins: fastestmirror, refresh-packagekit, security Setting up Install Process Loading mirror speeds from cached hostfile * base: mirror.nbrc.ac.in * epel: mirrors.aliyun.com * extras: mirror.nbrc.ac.in * updates: mirror.nbrc.ac.in Resolving Dependencies --> Running transaction check ---> Package edb-pgpool37.x86_64 0:3.7.7-1.rhel6 will be installed --> Finished Dependency Resolution Dependencies Resolved =============================================================================================================================================================================================================== Package Arch Version Repository Size =============================================================================================================================================================================================================== Installing: edb-pgpool37 x86_64 3.7.7-1.rhel6 enterprisedb-tools 473 k Transaction Summary =============================================================================================================================================================================================================== Install 1 Package(s) Total download size: 473 k Installed size: 1.9 M Is this ok [y/N]: y Downloading Packages: edb-pgpool37-3.7.7-1.rhel6.x86_64.rpm | 473 kB 00:02 Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Installing : edb-pgpool37-3.7.7-1.rhel6.x86_64 1/1 /sbin/ldconfig: File /usr/lib/libpq.so.5.8 is empty, not checked. Verifying : edb-pgpool37-3.7.7-1.rhel6.x86_64 1/1 Installed: edb-pgpool37.x86_64 0:3.7.7-1.rhel6 Complete! However, if you do not have an internet connection, or the EDB repository is not configured. You can manually download the dependent objects from the yum repository of enterprisedb and install them manually. [root@localhost pgpool37]# ls edb-as11-server-libs-11.2.9-1.rhel6.x86_64.rpm edb-pgpool37-3.7.7-1.rhel6.x86_64.rpm [root@localhost pgpool37]# rpm -ivh edb-pgpool37-3.7.7-1.rhel6.x86_64.rpm error: Failed dependencies: ppas-libs is needed by edb-pgpool37-3.7.7-1.rhel6.x86_64 [root@localhost pgpool37]# rpm -ivh edb-as11-server-libs-11.2.9-1.rhel6.x86_64.rpm Preparing... ########################################### [100%] 1:edb-as11-server-libs ########################################### [100%] [root@localhost pgpool37]# rpm -ivh edb-pgpool37-3.7.7-1.rhel6.x86_64.rpm Preparing... ########################################### [100%] 1:edb-pgpool37 ########################################### [100%]

vipulshukla

As a resolution to a few issues raised earlier with the EnterpriseDB clients, that the PEM agent creates sevrer load in case there are too many databases to be monitored, EnterpriseDB has included another feature in PEM , which allows us to configure the PEM agent connections through PgBouncer. This would help us pool the agent connections for better handling of both local and remote agents. PEM Version: 7.6 Recommended pgBouncer version 1.9 You could use the below set of steps to configure PEM agent to work with pgbouncer. The below example steps are to configure the local PEM agent to connect pgbouncer. Step 1: Configure Database. >> Create a user named pgbouncer on the pem database. CREATE USER pgbouncer PASSWORD 'edb'; >> Create another user pem_adm1. CREATE USER pem_adm1 PASSWORD 'edb' LOGIN CREATEROLE; >> Grant privileges to the new users. GRANT pem_admin TO pem_adm1; GRANT pem_agent_pool TO pem_adm1; GRANT connect ON pem DATABASE TO pgbouncer; GRANT usage ON pem SCHEMA TO pgbouncer; GRANT execute ON FUNCTION pem.get_agent_pool_auth(text) TO pgbouncer; >> Create PEM proxy user. SELECT pem.create_proxy_agent_user('agent_user1'); >> Add an entry for the new users in the pg_hba.conf file of the PEM server. local pem pgbouncer,pem_adm1 md5 >> Restart the PEM database server. systemctl restart edb-as-10.service >> Change PgBouncer directory ownership. chown postgres:postgres /opt/PgBouncer chmod 700 /opt/PgBouncer >> Make sure certificate path is correct in pgbouncer.ini >> Copy the user details in userlist.txt file. Login to pem database and run: COPY ( SELECT 'pgbouncer'::text, 'edb' UNION ALL SELECT 'pem_adm1'::text, 'edb' ) TO '/opt/PgBouncer/etc/userlist.txt' WITH (FORMAT CSV, delimiter ' ', FORCE_QUOTE * ); Step 2: Configure pgbouncer. >> Edit hba_file for pgbouncer and add below: hostssl pem agent_user1 0.0.0.0/0 cert >> Change owner of userlist to postgres and permission to 600. chown postgres:postgres /opt/PgBouncer/etc/userlist.txt chmod 600 /opt/PgBouncer/etc/userlist.txt >> Enable and start pgbouncer service systemctl enable pgbouncer.service systemctl start pgbouncer.service Step 3: Configure PEM Agent >> Register PEM agent using the agent_user1 user. PGSSLMODE=require PEM_SERVER_PASSWORD=edb /opt/edb/pem/agent/bin/pemworker --register_agent --pem-server <host> --pem-port 6432 --pem_user pem_adm1 --pem_agent_user agent_user1 --display-name PGBAgent1 This will create new certificates at /root/.pem/ and update the agent.cfg with new entries like new port, new agent id. >> Update the pem agent table to disable the current agent registration. Connect to pem database and run below: UPDATE pem.agent SET active='f' WHERE id=1; >> Add new entry to hba_file to allow access to pgbouncer local pgbouncer all md5 local pem pem_adm1 md5 >> Restart pgbouncer service. systemctl restart pgbouncer.service >> Connect to the pem database using agent_user1 to confirm connetcivity using below: PGSSLMODE=require PGSSLCERT=/root/.pem/agent_user1.crt PGSSLKEY=/root/.pem/agent_user1.key PEM_SERVER_PASSWORD=edb /opt/PostgreSQL/10/bin/psql -p 6432 -U agent_user1 -d pem >> Backup and Remove old certificates from /root/.pem/ >> Restart the pem agent service systemctl restart pemagent.service >> Change pem server properties. (agent tab) Right click on the server > properties > Agent tab. SELECT the new agent (PGBAgent1) from the drop down. >> Check the agent in the monitoring tab of PEM client. You should now see the agent PGBAgent1 instead of the default agent 'Postgres Enterprise Manager Host'.

vipulshukla

The statement "the ON CONFLICT DO NOTHING clause is supported, provided a unique index inference specification is omitted." in the document means that if we do not use the unique index reference( in your case id) along with the ON CONFLICT DO NOTHING, then only it will work. Refer document on the link below for detailed information. https://www.postgresql.org/docs/11/postgres-fdw.html According to the document the below statement will not work as we have mentioned the unique index reference with the on conflict clause. targetdb=# insert into test1 values (2,'Amit') on conflict(id) do nothing; ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification Whereas, the below statement will work, as we do not mention the " id " which was specified as a unique index in the source table. targetdb=# insert into test1 values (2,'Amit') on conflict do nothing; INSERT 0 0 Below is the complete scenario: On source server ============== foreign_db=# create table s2.t3 (id integer unique, data text); CREATE TABLE foreign_db=# select * from s2.t3; id | data ----+------ 10 | dfgh 15 | sw 90 | sw foreign_db=# insert into s2.t3 values (10,'dfgh'); 2019-02-02 05:29:39.748 PST [63940] ERROR: duplicate key value violates unique constraint "t3_id_key" 2019-02-02 05:29:39.748 PST [63940] DETAIL: Key (id)=(10) already exists. 2019-02-02 05:29:39.748 PST [63940] STATEMENT: insert into s2.t3 values (10,'dfgh'); ERROR: duplicate key value violates unique constraint "t3_id_key" DETAIL: Key (id)=(10) already exists. On target server ============== postgres=# CREATE SERVER foreign_server postgres-# FOREIGN DATA WRAPPER postgres_fdw postgres-# OPTIONS (host 'localhost', port '5433', dbname 'foreign_db'); CREATE SERVER postgres=# CREATE USER MAPPING FOR postgres postgres-# SERVER foreign_server postgres-# OPTIONS (user 'postgres', password 'edb'); CREATE USER MAPPING postgres=# CREATE FOREIGN TABLE test3 ( postgres(# id integer NOT NULL, postgres(# data text postgres(# ) postgres-# SERVER foreign_server postgres-# OPTIONS (schema_name 's2', table_name 't3'); CREATE FOREIGN TABLE postgres=# select * from test3; id | data ----+------ 10 | dfgh 15 | sw 90 | sw The following statement will work, as we do not mention the " id " which was specified as a unique index in the source table. postgres=# insert into test3 values (90,'sw') on conflict do nothing; INSERT 0 0 But the below statement will not work as we have mentioned the unique index reference with the on conflict clause. postgres=# insert into test3 values (90,'sw') on conflict(id) do nothing; ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

vipulshukla · ‎03-17-2019

Requirements: >> wal_level should be >= logical Limitations of Logical Replication: >> Schema replication is not supported. >> DDL(s) replication is not supported. >> Replication of sequences is not supported. >> TRUNCATE table is not supported. >> Replication only works for base tables. >> Replication does not work within the databases on the same cluster. In the test case below, IP 192.168.0.114 is the IP address of my master(publication server) and 192.168.0.115 is for the slave (subscription server). Create Publication: Creating a publication for logical replication is easy. You can either provide the list of the tables that you need to add to publication, or keyword 'ALL TABLES' could be used to include all the tables. To create the publication, login to the source(master) database. >> CREATE PUBLICATION pub2 FOR TABLE testtab1, testtab2; OR >> CREATE PUBLICATION pub2 FOR ALL TABLES; Create Subscription: While creating the subscription, you would just need to provide a connection string for the master server and the name of the publication to which you want to connect. >> CREATE SUBSCRIPTION sub2 CONNECTION 'host=192.168.0.114 dbname=postgres port=5432 password=edb' PUBLICATION pub2; Please make sure that you have allowed connections from the remote machine to the publication database cluster(192.168.0.114). This could be done configuring 'listen_address' to '*' or an specific IP address. And adding pg_hba.conf entry for the destination(slave) server. for ex: host all all 192.168.0.115/32 md5 Adding Tables to Publication: Tables can ve added to the existing publication using ALTER PUBLICATION, as below: >> ALTER PUBLICATION pub2 ADD TABLE testtab3; In order to apply the changes on an existing subscription, the above command should be followed by below command on slave databases (192.168.0.115). >> ALTER SUBSCRIPTION sub2 REFRESH PUBLICATION; TEST CASE: ON MASTER >> postgres=# create table testtab1(id int primary key); CREATE TABLE postgres=# insert into testtab1 values (generate_series(1,100)); INSERT 0 100 postgres=# create table testtab2(id int primary key); CREATE TABLE postgres=# insert into testtab2 values (generate_series(1,1000)); INSERT 0 1000 postgres=# create publication pub2 for table testtab1, testtab2; CREATE PUBLICATION ON SLAVE >> postgres=# create table testtab1(id int primary key); CREATE TABLE postgres=# create table testtab2(id int primary key); CREATE TABLE postgres=# create subscription sub2 connection 'host=localhost dbname=postgres port=5432 password=edb' publication pub2; NOTICE: created replication slot "sub2" on publisher CREATE SUBSCRIPTION postgres=# select count(*) from testtab1 ; count ------- 100 (1 row) postgres=# select count(*) from testtab2 ; count ------- 1000 (1 row) ON MASTER >> (Adding Table) postgres=# create table testtab3(id int primary key); CREATE TABLE postgres=# insert into testtab3 values (1); INSERT 0 1 postgres=# alter publication pub2 add table testtab3 ; ALTER PUBLICATION ON SLAVE >> (Applying Changes) postgres=# create table testtab3(id int primary key); CREATE TABLE postgres=# alter subscription sub2 refresh publication ; ALTER SUBSCRIPTION postgres=# select count(*) from testtab3 ; count ------- 1 (1 row)

About vipulshukla

PostgreSQL Parameters Affected By EDB Dynatune

Adding policy for Data Redaction

Re: A Real Integration: EDB Failover Manager and P...

PgBouncer with PEM 7.6

"INSERT ON CONFLICT DO NOTHING" with postgre_fdw f...

Setting Up Logical Replication on PostgreSQL 10