AWS -Aurora PostgreSQL Queries & Failover

The following code Illustrates connecting to the database pgdb on port 5432 using the cluster endpoint. As part of this illustration, the example creates a table named failover and inserts one row into the table.

psql  -h application-autoscaling-1bce6a4-xxxxxxxxxx.us-east-2.rds.amazonaws.com -d pgdb -p 5432 -U pgadmin

--Listing the name of master instance 

pgdb=> select server_id from aurora_replica_status() where session_id='MASTER_SESSION_ID';

       server_id

-----------------------

 myinstance-us-east-2a

(1 row)

pgdb=> CREATE TABLE FAILOVER(FAILOVERNAME  VARCHAR(20), FAILOVERTYPE INTEGER);

CREATE TABLE                          

pgdb=>

pgdb=> INSERT INTO FAILOVER VALUES ('TEST-1',1 );

INSERT 0 1

pgdb=>

pgdb=> SELECT * FROM FAILOVER;

  failovername   | failovertype

-----------------+--------------

 TEST-1          |            1

(1 row)

Check running queries and diagnose resource consumption issues for my Amazon RDS or Aurora PostgreSQL DB instance

 Run the following command:

SELECT * FROM pg_stat_activity ORDER BY pid;

You can also modify this command to view the list of running queries ordered by when the connections were established:

SELECT * FROM pg_stat_activity ORDER BY backend_start;

If the column value is null, then there is no transaction opened in that session:

SELECT * FROM pg_stat_activity ORDER BY xact_start;

Or, view the same list of running queries ordered by when the last query was executed:

SELECT * FROM pg_stat_activity ORDER BY query_start;

For an aggregated view of the wait events, if there are any, run the following command:

select state, wait_event, wait_event_type, count(*) from pg_stat_activity group by 1,2,3 order by wait_event;

Diagnose resource consumption

 (1)  Identify the query that is consuming resources

 (2)  Identify the current activity of the session by running the following command:

      SELECT * FROM pg_stat_activity WHERE pid = PID;

To stop the process that is executing the query, invoke the following query from another session. Be sure to replace PID with the pid of the process that you identified in step 2.

     SELECT pg_terminate_backend(PID);

Determining whether applications are connecting to Aurora PostgreSQL DB clusters using SSL

select datname, usename, ssl, client_addr from pg_stat_ssl inner join pg_stat_activity on pg_stat_ssl.pid = pg_stat_activity.pid where ssl is true and usename<>'rdsadmin';

Displaying volume status for an Aurora PostgreSQL DB cluster

SELECT * FROM aurora_show_volume_status(); 

postgres=> SHOW stats_temp_directory;