Search found 69 matches

by Admin
Sun Dec 27, 2020 3:49 pm
Forum: Postgres DB
Topic: Postgres DB backup and restore approaches
Replies: 0
Views: 128

Postgres DB backup and restore approaches

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...
by Admin
Sun Dec 27, 2020 3:11 pm
Forum: Postgres DB
Topic: Check Postgres DB tables and their index(es) size
Replies: 0
Views: 72

Check Postgres DB tables and their index(es) size

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...
by Admin
Sun Dec 27, 2020 3:10 pm
Forum: Postgres DB
Topic: Check bloat in Postgres DB
Replies: 0
Views: 69

Check bloat in Postgres DB

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...
by Admin
Sun Dec 27, 2020 3:09 pm
Forum: Postgres DB
Topic: Find Postgres tables without primary key
Replies: 0
Views: 53

Find Postgres tables without primary key

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...
by Admin
Sun Dec 27, 2020 3:06 pm
Forum: Postgres DB
Topic: Run vacuum and analyze on all Postgres databases or on specific Postgres database
Replies: 0
Views: 61

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

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

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

Code: Select all

vacuumdb -h <hostname> -p <port> -U postgres -j 4 -z -v -a

vacuumdb -h <hostname> -p <port> -U postgres -j 4 -z -v <database>
by Admin
Sun Dec 27, 2020 3:04 pm
Forum: DevOps
Topic: Kitchen Sync - Fast unidirectional synchronization of database
Replies: 0
Views: 129

Kitchen Sync - Fast unidirectional synchronization of database

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...
by Admin
Sun Dec 27, 2020 2:57 pm
Forum: Postgres DB
Topic: List DB Indexes and their definition
Replies: 0
Views: 53

List DB Indexes and their definition

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...
by Admin
Sun Dec 27, 2020 2:55 pm
Forum: Open source tools
Topic: Lens - Kubernetes IDE
Replies: 0
Views: 123

Lens - Kubernetes IDE

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...
by Admin
Sun Dec 27, 2020 2:41 pm
Forum: Postgres DB
Topic: Compare two databases in Postgres: tables and indexes
Replies: 0
Views: 68

Compare two databases in Postgres: tables and indexes

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 ...
by Admin
Sat Dec 12, 2020 2:30 pm
Forum: Oracle DB
Topic: How to defragment / shrink table and indexes in Oracle?
Replies: 0
Views: 111

How to defragment / shrink table and indexes in Oracle?

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...