dm8ry.com

Postgres DB backup and restore approaches Get dump for Postgres DB: pg_dump -h host -p port db_name > db_name.sql To dump a database into a directory-format archive in parallel with 4 worker jobs: mkdir my_db_dumps pg_dump -h host -p port -Fd db_name -j 4 -f my_db_dumps_dir To dump a database schem...

Check Postgres DB tables and their index(es) size SELECT table_name, pg_size_pretty(table_size) AS table_size, pg_size_pretty(indexes_size) AS indexes_size, pg_size_pretty(total_size) AS total_size FROM ( SELECT table_name, pg_table_size(table_name) AS table_size, pg_indexes_size(table_name) AS inde...

Check bloat in Postgres DB SELECT current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/ ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::float/otta END)::numeric,1) AS tbloat, CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbyt...

Find Postgres tables without primary key select tab.table_schema, tab.table_name from information_schema.tables tab left join information_schema.table_constraints tco on tab.table_schema = tco.table_schema and tab.table_name = tco.table_name and tco.constraint_type = 'PRIMARY KEY' where tab.table_ty...

Run vacuum and analyze on all Postgres databases or on specific Postgres database.

Ability to run the task in parallel (multi threading).

Goal: Fast unidirectional synchronization - make or efficiently update a copy of a database, without slow dumping & reloading. Kitchen Sync aims to: Finish the job much faster than full reloads: working in parallel and only modifying the data that's actually changed, Kitchen Sync is usually 5-20 tim...

List DB Indexes and their definition SELECT n.nspname as "schema" ,t.relname as "table" ,c.relname as "index" ,pg_get_indexdef(indexrelid) as "def" FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid JOIN pg_catalog.p...

Lens is the only IDE you’ll ever need to take control of your Kubernetes clusters. It's open source and free. Download it today! https://k8slens.dev/ Lens is the most powerful IDE for people who need to deal with Kubernetes clusters on a daily basis. It is a standalone application for MacOS, Windows...

Compare two databases in Postgres: tables and indexes compare_2_dbs_postgres.sh #!/bin/bash ################################################################################################################################## # # Name: Compare 2 Postgres DBs # # Description: Compare Tables and Indexes ...

How to defragment / shrink table and indexes in Oracle? sqlplus -s ... set lines 1200 set pages 100 select segment_name, sum(bytes)/1024/1024/1024 "Size in GB" from dba_segments where table_name in ('<table_name>') group by segment_name order by 2 desc; select segment_name, sum(bytes)/1024/1024/1024...