Posts

Showing posts from January, 2025

Postgres: To get duration of a script

(1) Enable the extension (if not already enabled): CREATE EXTENSION pg_stat_statements; (2) Check the execution time: SELECT query, total_time, calls  FROM pg_stat_statements WHERE query LIKE '%your_query%'; OR SELECT query, total_time, calls FROM pg_stat_statements WHERE query LIKE '%your_query%'; total_exec_time   double precision Total time spent executing the statement, in milliseconds

Postgres: To check indexes on a table

Image
 select * from pg_indexes where tablename = 'offers'; select      c.relnamespace::regnamespace as schema_name,     c.relname as table_name,     i.indexrelid::regclass as index_name,     i.indisprimary as is_pk,     i.indisunique as is_unique from pg_index i join pg_class c on c.oid = i.indrelid where c.relname = 'offers';  

Postgres: to setup in SQL Developer

 openssl pkcs8 -topk8 -inform PEM -outform DER -in /opt/striim/pg-coupon-cpntest_client-key.pem -out /opt/striim/pg-coupon-cpntest_client-key.pk8 -nocrypt 5432/cpntest?enableSSL=true&requireSSL=true&verifyServerCertificate=true&sslrootcert=O:\Users\GM15\keys\pg-coupon-cpntest_server-ca.pem&sslcert=O:\Users\GM15\keys\pg-coupon-cpntest_client-cert.pem&sslkey=O:\Users\GM15\keys\pg-coupon-cpntest_client-key.pk8&1=1

Postgres: To lock an account

 alter user striim nologin ; ALTER ROLE striim NOCREATEDB NOCREATEROLE NOINHERIT;

PeopleSoft: to start and stop elastic search

 cd $ES_HOME/bin >ps -ef | grep elast >ps -ef | grep cli root        6316       1  0 May05 ?        01:22:11 /usr/sbin/adclient smmsp    1173397       1  0 Jul10 ?        00:00:00 sendmail: Queue runner@01:00:00 for /var/spool/clientmqueue psoft    1328581 1327640  0 Jul18 pts/0    00:04:12 ./../node/bin/node ./../src/cli/dist nohup ./elasticsearch &    to start elasticsearch psoft-pseshrdevapp602:NONE:/usr/opt/app/es7/pt/Kibana7.10.0/bin>ls -lrt total 16 -rwxr-xr-x. 1 psoft psoft  813 Dec  9  2022 kibana-plugin -rwxr-xr-x. 1 psoft psoft  776 Dec  9  2022 kibana-keystore -rwxr-xr-x. 1 psoft psoft  838 Dec  9  2022 kibana -rw-------. 1 psoft psoft 3058 Jul 18 17:07 nohup.out psoft-pseshrdevapp602:NONE:/usr/opt/app/es7/pt/Kibana7.10.0/bin>rm nohup.out psoft-pseshrdevapp60...

Postgres: Manually analyze

  ANALYZE table_name; -- Analyzes the "table_name" table ANALYZE schema_name.table_name; -- Analyzes the "table_name" table within the "schema_name" schema ANALYZE database_name; -- Analyzes all tables within the "database_name" database

To convert SSL PEM certiicate to PK8 format

openssl pkcs8 -topk8 -inform PEM -outform DER -in pg-cusfit-cfstag_client-key.pem -out pg-cusfit-cfstag_client-key.pk8 -nocrypt From the Custom store microservice project in my local development environment application_local.yml datasource:    url: jdbc:postgresql://pg-cusfit-cftest.gcp.tmw.com:5432/cftest?ssl=true&sslmode=require&sslcert=C:/Apps/pgcerts/cftest//pg-cusfit-cftest_client-cert-1.pem&sslkey=C:/Apps/pgcerts/cftest/pg-cusfit-cftest_client-key-1.pk8    username: ordadmin    password: cftest_Ordadm1n    driver-class-name: org.postgresql.Driver

DB2: How to monitor db2fmp

  How to monitor db2fmp ? How to monitor db2fmp ? Technote (FAQ) Question How does the db2fmp process relate to an application? How do I determine which application is calling a routine executing inside this db2fmp process? What is the criteria to reuse db2fmp process? Answer From a DB2 perspective we can determine the application and routine calling the routine along with some diagnostics to help the developer that wrote the routine troubleshoot any performance problems. What is the criteria to reuse db2fmp process ? Threadsafe routines: One db2fmp can run many routines servicing multiple application handles (connections). Non-threadsafe routines: One db2fmp can run ONE routine servicing ONE application handle. By default DB2 pools inactive 32-bit (db2fmp32) and 64-bit (db2fmp) processes up to the value specified by FENCED_POOL. Checking the UNIX/Linux "ps" output to look for "db2fmp (idle)" processes indicates there is no db2fmp associated with a DB2 co-ordinator ...

Posgres: How long the database was up

 SELECT current_timestamp - pg_postmaster_start_time() AS up_time; select pg_postmaster_start_time () pg_postmaster_start_time  () →  timestamp with time zone Returns the time when the server started. 2025-01-17 16:57:52.622095+00 UTC to CST Time Converter https://www.utctime.net/utc-to-cst-converter

Postgres: To see showing database-wide statistics

  PostgreSQL: Documentation: 17: 27.2. The Cumulative Statistics System https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW select * from pg_stat_database;

Postgres: Find last query executed by session

  select pid, usename as username, datname as database_name, query , application_name, backend_start, state, state_change from pg_stat_activity where pid = 'chosen_session' ;

Postgres: Last Auto-Vacuum, Vacuum, Auto-Analyzed Analyzed Date

  select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;

Postgres: To check column default value

 SELECT column_name, column_default FROM information_schema.columns WHERE (table_schema, table_name) = ('cpnadmin', 'coupons') ORDER BY ordinal_position;

Postgres: Clean up stopped replication slot

Image
  When a slot is inactive, that means that Murty and his team have stopped the replication on their side. The SQL below will identify inactive slots. We should schedule this script in a job , running every hour.    (1) SELECT  slot_name,  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) AS size,  active,  slot_type  FROM pg_replication_slots;       (2) Once you identify the replication slot, the job should drop it with the following command:     SELECT pg_drop_replication_slot ('pg_replication_slot_name_from_above');         (3) Restart the database instance to clean up unwanted disk space.   Notes from Google:- Thank you for contacting Google Cloud Support. My name is Jagadeesh and I will be assisting you with this issue.   I understand that WAL logs are expanding to over 1.5TB and you want to know what is causing WAL logs to grow and you want to reduce it. Please confirm if ...

Oracle: Refresh Database From HotBackup

 1. bring down the target environment (fsdvlp) - appservers, process schedulers, database server 2. mkdir /usr/opt/app/dbdump/hot_backup/FSPROD on the target server (psfststdb01) 3. mkdir /usr/opt/app/dbdump/hot_backup/fsprod on the target server (psfststdb01) 3. copy all the necessary files to ..../fsprod and .../FSPROD   on the source, prod server (psfsdb01)                              --20mins  - 98GB 4. tar up the files in ../temp    tar cf backupfiles.tar *                                                                    --15mins  -50GB    5. ensure /usr/opt/app/dbdump/hot_backup/FSPROD is cleanedup on target server 6. 'scp' the tar file from ../temp to oracle@psfststdb01:/usr/opt/app/dbdump/ho...