Showing posts with label Postgres. Show all posts
Showing posts with label Postgres. Show all posts

April 25, 2023

initdb PostgreSQL tool

Postgres initdb utility


initdb initializes/creates a new PostgreSQL database cluster.

initdb --help

Usage:
  initdb [OPTION]... [DATADIR]

Options:
  -A, --auth=METHOD          default authentication method for local connections
      --auth-host=METHOD    default authentication method for local TCP/IP connections
      --auth-local=METHOD    default authentication method for local-socket connections
  -D, --pgdata=]DATADIR      location for this database cluster, data directory (if not specified, PGDATA will be used)
  -E, --encoding=ENCODING    set default encoding for new databases
  -g, --allow-group-access  allow group read/execute on data directory
      --icu-locale=LOCALE    set ICU locale ID for new databases
  -k, --data-checksums      use data page checksums
      --locale=LOCALE        set default locale for new databases
      --lc-collate=, --lc-ctype=, --lc-messages=LOCALE
      --lc-monetary=, --lc-numeric=, --lc-time=LOCALE   set default locale in the respective category for new databases (default taken from environment)
      --no-locale            equivalent to --locale=C
      --locale-provider={libc|icu}   set default locale provider for new databases
      --pwfile=FILE          read password for the new superuser from file
  -T, --text-search-config=CFG default text search configuration
  -U, --username=NAME        database superuser name
  -W, --pwprompt              prompt for a password for the new superuser, to force super user to provide password
  -X, --waldir=WALDIR        location for the write-ahead log directory
      --wal-segsize=SIZE      size of WAL segments, in megabytes

Less commonly used options:
  -d, --debug                 generate lots of debugging output
      --discard-caches      set debug_discard_caches=1
  -L DIRECTORY             where to find the input files
  -n, --no-clean             do not clean up after errors
  -N, --no-sync              do not wait for changes to be written safely to disk
      --no-instructions     do not print instructions for next steps
  -s, --show                  show internal settings
  -S, --sync-only           only sync database files to disk, then exit

Other options:
  -V, --version             output version information, then exit
  -?, --help                show this help, then exit

initdb --version
initdb -D /usr/local/pgsql/data
initdb -D /data/postgres/9.5/5301 -U
initdb -D /data/postgres/14.6/5301 -W
initdb -D /usr/edbstore --wal-segsize 1024 -W
initdb -D /data/postgres/15.2/5301 --pwprompt --username=postgresadmin


Related PostgreSQL Articles: pg_upgrade utility in PostgreSQL

April 18, 2023

Postgres pg_verifybackup tool

pg_verifybackup tool in PostgreSQL


pg_verifybackup verifies a backup against the backup manifest. New backup verification tool was introduced in Postgres 13.

pg_verifybackup --help

Usage:
  pg_verifybackup [OPTION]... BACKUPDIR

Options:
  -e, --exit-on-error          exit immediately on error
  -i, --ignore=RELATIVE_PATH  ignore indicated path
  -m, --manifest-path=PATH    use specified path for manifest
  -n, --no-parse-wal          do not try to parse WAL files
  -q, --quiet                  do not print any output, except for errors
  -s, --skip-checksums        skip checksum verification
  -w, --wal-directory=PATH    use specified path for WAL files
  -V, --version                output version information, then exit
  -?, --help                  show this help, then exit

pg_verifybackup --version
pg_verifybackup c:\pgbackup
pg_verifybackup /backup/postgres
pg_verifybackup /backup/postgres --no-parse-wal --quiet
pg_verifybackup /backup/postgres --skip-checksums -q


Related Postgres Articles:  PostgreSQL pg_basebackup utility

March 5, 2023

pglogical replication between PostgreSQL RDS/Aurora Postgres databases

Replication setup between Amazon Aurora Postgres/PostgreSQL RDS instances using pglogical


pglogical extension provides additional capabilities that aren’t available in native PostgreSQL.
Publish/subscribe model to migrate critical workloads.
Used to migrate data between PostgreSQL versions and replicate data between two or more PostgreSQL instances regardless of where those instances are located.
Used to migrate whole clusters, specific tables, and specific rows/columns.

Limitations of pglogical:
  • Needs superuser privileges
  • UNLOGGED/TEMPORARY tables/views can't be replicated
  • Primary key or unique key is mandatory
  • Sequence data is not replicated
  • Automatic DDL replication is not supported (other than TRUNCATE)
  • It is not recommended to add additional UNIQUE constraints other than the PRIMARY KEY
  • Unique constraints must not be deferrable
  • Large objects (LOBs) are not replicated
  • Foreign key constraints aren’t enforced during the replication process

Nodes - PostgreSQL instances
Providers and Subscribers - roles taken by Nodes
Replication Set - a collection of tables; used to control which tables in the database are replicated and which actions on those tables are replicated.

Step #1: Parameter changes, on both source/target databases

Cluster parameter group   -> some parameters are static, needs reboot of cluster
rds.logical_replication = 1
shared_preload_libraries = ***, pglogical
wal_level = 'logical'
max_worker_processes = 10    # one per database needed on provider node and one per node needed on subscriber node
max_replication_slots = 10  # one per node needed on provider node
max_wal_senders = 10        # one per node needed on provider node
track_commit_timestamp = on # needed for last/first update wins conflict resolution

Instance parameter group  -> parameter is static, needs reboot of instance
shared_preload_libraries = ***, pglogical

If debugging/logging more information required, please set below parameters.
log_min_messages=debug1
log_min_error_statement=debug1
log_error_verbosity=verbose
log_statement = 'all'

Step #2: Create user & extension, on both source/target databases

create user pguser with password 'pguser123';
grant rds_superuser to pguser;
 
create extension pglogical;

Step #3: Create Provider node (source database)

select pglogical.create_node(node_name := 'provider1', dsn := 'host=providerhost port=5432 dbname=db');
select pglogical.create_node(node_name :='node1', dsn := 'host=<db-endpoint> port=5432 dbname=<dbname> user=pguser password=pguser123);
select pglogical.create_node(node_name := 'node1', dsn := 'host= lab-pg14.xxxxxx.us-east-2.rds.amazonaws.com port=5432 sslmode=require dbname=cluster1 user=pguser password=xxxx);

select pglogical.drop_node(node_name := 'node1234');         # if needed to recreate node

Step #4: Create replication set and add tables to replication set, on source database

There are 3 preexisting replication sets named "default", "default_insert_only" and "ddl_sql".

select pglogical.create_replication_set(set_name name, replicate_insert bool, replicate_update bool, replicate_delete bool, replicate_truncate bool)
select pglogical.create_replication_set('shared_repl', true, true, true, true );
select pglogical.create_replication_set('test_repl_set');
select pglogical.create_replication_set(set_name := 'dml-replication-set', replicate_insert := TRUE, replicate_update := TRUE, replicate_delete := TRUE, replicate_truncate := FALSE);
select pglogical.alter_replication_set(set_name name, replicate_inserts bool, replicate_updates bool, replicate_deletes bool, replicate_truncate bool)

select pglogical.replication_set_add_all_tables('default', ARRAY['public']);      # adds all tables from schema 'public' to 'default' replication set
select pglogical.replication_set_add_all_tables('replication_set', ARRAY['catalog2'], true);
select pglogical.replication_set_add_all_tables('test_repl_set', ARRAY['schematest']);

select pglogical.replication_set_add_table(set_name name, relation regclass, synchronize_data boolean, columns text[], row_filter text)
select pglogical.replication_set_add_table('default_insert_only', 'public.pgbench_history');
select pglogical.replication_set_add_table(set_name := 'replication_set', relation := 'test', synchronize_data := true );
select pglogical.replication_set_add_table(set_name := 'test_repl_set', relation := 'schematest.employees');

select pglogical.replication_set_add_all_sequences(set_name name, schema_names text[], synchronize_data boolean)
select pglogical.replication_set_add_all_sequences(set_name := 'default', schema_names := '{public}'::text[], synchronize_data := true)
select pglogical.replication_set_add_sequence(set_name name, relation regclass, synchronize_data boolean)
select pglogical.synchronize_sequence( seqoid ) from pglogical.sequence_state;
select seqoid::regclass from pglogical.sequence_state"

select pglogical.drop_replication_set('default');     # if needed to recreate replication set
select pglogical.replication_set_remove_table(set_name name, relation regclass)
select pglogical.replication_set_remove_table('default', 'listing');

Step #5: Create subscription, on target database

select pglogical.create_subscription(subscription_name := 'subscription1', provider_dsn := 'host=providerhost port=5432 dbname=db');
select pglogical.create_subscription(
    subscription_name := 'west_sub',
    provider_dsn := 'host=testdb.cluster-****.us-west-2.rds.amazonaws.com port=5433 dbname=postgres user=pguser password=pguser123',
    replication_sets := ARRAY['default'],
    synchronize_data := false,
    forward_origins := '{}' );
select pglogical.create_subscription(subscription_name := 'cluster2_sub', provider_dsn := 'host=lab-pg14.xxxxxx.us-east-1.rds.amazonaws.com port=5434 sslmode=require dbname=cluster1 user=pguser password=xxxx', replication_sets := ARRAY['default'], synchronize_data := true, forward_origins := '{}' );

select pglogical.alter_subscription_disable('subscription_name');
select pglogical.alter_subscription_disable(subscription_name) from pglogical.subscriptions where writer = 'name_of_writer';
select pglogical.alter_subscription_enable('west_to_east2_sub');
select pglogical.alter_subscription_enable('west_to_east2_sub', true);
select pglogical.drop_subscription('subscription1');

select pglogical.alter_subscription_skip_changes_upto('subscription_name','the_target_lsn');
select pglogical.wait_for_subscription_sync_complete('subscription1');

Step #6: Commands to check pglogical replication status

select pglogical.show_subscription_status('west_sub');
select pglogical.show_subscription_table(subscription_name name, relation regclass)
select pglogical.show_subscription_table('west_sub','listing');

select * from pglogical.subscription;
select subscription_name, status from pglogical.show_subscription_status() order by subscription_name;
select * from pglogical.show_subscription_status();

select * from pg_replication_slots;
select slot_name, slot_type, active from pg_replication_slots;
select slot_name from pg_replication_slots where active='f'; # inactive replications slots
select redo_lsn, slot_name, restart_lsn, round((redo_lsn-restart_lsn)/1024/1024/1024, 2) AS GB_behind from pg_control_checkpoint(), pg_replication_slots;

To remove replication slot, if required:
select pg_terminate_backend(23593);
select pg_drop_replication_slot('pgl_postgres_node_east_east_to_west_sub');

select * from pg_stat_replication_slots;
select slot_name, spill_txns, spill_count, spill_bytes from pg_stat_replication_slots;
select stream_txns, stream_count, stream_bytes from pg_stat_replication_slots;

select * from pg_stat_replication;
select pid, usename, application_name, client_addr, state, sync_state,
       pg_wal_lsn_diff(sent_lsn, write_lsn) as write_lag, pg_wal_lsn_diff(sent_lsn, flush_lsn) as flush_lag, 
       pg_wal_lsn_diff(sent_lsn, replay_lsn) as replay_lag, pg_wal_lsn_diff(sent_lsn, replay_lsn) as total_lag 
from pg_stat_replication;

select * from pglogical.depend;
select * from pglogical.node order by node_name;
select * from pglogical.local_node;
select * from pglogical.pglogical_node_info();

select * from pglogical.local_sync_status;
SELECT * FROM pglogical.local_sync_status WHERE NOT sync_status = 'r';
SELECT sync_status FROM pglogical.local_sync_status WHERE sync_nspname = 'public' AND sync_relname = 'example';
sync_kind
▪ i initial sync
▪ d data sync
▪ f full sync
▪ s structure sync

sync_status
• \0: No sync
• i: initial - Ask for sync
• r: sync done
• d: data sync
• c: constraints sync
• s: structure sync
• u: Catching up
• w: Table sync is waiting (to get OK from main thread)
• y: sync finished at LSN

select * from pglogical.replication_set order by set_name;
select * from pglogical.replication_set_table;
select * from pglogical.replication_set_table where set_reloid::text like '%listing%';
select * from pglogical.queue;

select * from pg_publication;
select * from pg_publication_tables;
select * from pg_stat_subscription;

select pglogical.alter_subscription_resynchronize_table(subscription_name name, relation regclass)
select pglogical.alter_subscription_resynchronize_table('east2_to_west2_sub', 'venue');


Related PostgreSQL Articles: 30 Best PostgreSQL DBA Interview Questions  pg_restore - PostgreSQL database/backup restore tool


November 26, 2022

Top New Features in PostgreSQL 13/Postgres 12 database

Postgres 13 new features


PostgreSQL 13 has many improvements and new features.

  • B-Tree index de-duplication, which will save space and improve performance
  • Parallelized vacuuming of indexes - PARALLEL option has been added
  • Incremental sorting - will use existing sorted data. New parameter enable_incremental_sort
  • In PostgreSQL13, explain command can track wal_usage
  • Significant improvements in indexing and lookup system, those benefit databases
  • Improved performance of queries that use aggregates or partitioned tables
  • PostgreSQL 13 has improvements in partition pruning
  • Trusted extensions - can install extensions without superuser privileges
  • New tool pg_verifybackup to verify backups
  • Renamed parameter wal_keep_segments to wal_keep_size
  • Can forcefully drop the database, even when sessions are connected
    • DROP DATABASE db33 with (force);
    • dropdb ... -f

New System Views in Postgres 13:
  • pg_stat_progress_basebackup
  • pg_stat_progress_analyze

New Parameters in PostgreSQL 13:
  • autovacuum_vacuum_insert_threshold
  • autovacuum_vacuum_insert_scale_factor
  • enable_incremental_sort
  • maintenance_io_concurrency
  • max_parallel_maintenance_workers
  • min_parallel_index_scan_size


Postgres 12 new features

  • New datatype JSONPATH was introduced, for binary representation of the parsed SQL/JSON path expression.
  • PostgreSQL 12 provides several functions to use JSONPath to query your JSONB data.
  • PostgreSQL 12 introduced the ability to add partitions without the need to take an exclusive lock on the parent table and changed the planning algorithm.


Related PostgreSQL Articles:  
pg_restore PostgreSQL tool    New Features in PostgreSQL 14 database

November 25, 2022

40 Best (Aurora) PostgreSQL Interview Questions - part 2

40 Top PostgreSQL/Aurora Postgres DBA Interview Questions - Part2


Q21. Which are the methods PostgreSQL provides to create a new database?
Answer: PostgreSQL provides the following methods to create a new database:
  • Using CREATE DATABASE, an SQL command
  • Using created a command-line executable

Q22. How do you delete the database in PostgreSQL?
Answer: We can delete the database by using any one of the below options:
  • Using DROP DATABASE, an SQL command
  • Using dropdb a command-line executable

Q23. What is table partitioning in Postgres?
Answer:

Q24. Explain PostgreSQL Inheritance. How does PostgreSQL implement table inheritance? What is the use of it?
Answer:

Q25. What types of indexes are supported in PostgreSQL?
Answer:

Q26. How to take the backup of PostgreSQL?
Answer:

Q27. What is a CTID of PostgreSQL?
Answer:

Q28. What are the different datatypes in PostgreSQL?
Answer:

Q29. How are stats/statistics updated in PostgreSQL?
Answer:

Q30. How to start a PostgreSQL database server?
Answer:

Q31. How To stop the PostgreSQL database server?
Answer:

Q32. How to check the status of the PostgreSQL server running or not?
Answer:

Q33. How to get a list of databases in a PostgreSQL cluster?
Answer:

Q34. How to take differential or incremental backup of PostgreSQL database?
Answer: There is NO utility for differential or incremental backups.

Q35. How to upgrade the Postgres database server?
Answer:

Q36. How crash recovery works in Postgres?
Answer:

Q37. What are the default tablespaces created in Postgres?
Answer:

Q38. What are the levels of isolation?
Answer:

Q39. How to setup logical replication between two AWS Aurora Postgres clusters?
Answer:

Q40. 
Answer:


Related Postgres Articles: PostgreSQL/Aurora Postgres Database Interview Questions Part1

July 28, 2021

vacuumdb utility commands in PostgreSQL

PostgreSQL vacuumdb utility commands


vacuumdb  - utility for cleaning/analyzing PostgreSQL database. Will also generate internal statistics used by the PostgreSQL query optimizer.

vacuumdb --version

vacuumdb proddb
vacuumdb -U postgres -d testdb
vacuumdb -U postgres -a

vacuumdb -f proddb # full vacuum
vacuumdb -U postgres -d testdb -f -v
vacuumdb -U postgres -a -f --verbose

vacuumdb -F # Vacuum freeze
vacuumdb -Z proddb
vacuumdb -z proddb  --echo
vacuumdb -az
vacuumdb -azf

nohup vacuumdb -f -d empown -h testdb.cluster-***.us-east-2.rds.amazonaws.com -p5432 -U dbadmin -v &
nohup vacuumdb --full --analyze --dbname empown --host testdb.cluster-***.us-east-2.rds.amazonaws.com --port5432 --username dbadmin --verbose &

vacuumdb --help
Usage:
  vacuumdb [OPTION]... [DBNAME]

Options:
  -a, --all                       vacuum all databases
  -d, --dbname=DBNAME             database to vacuum
      --disable-page-skipping     disable all page-skipping behavior
  -e, --echo                      show the commands being sent to the server
  -f, --full                      do full vacuuming
  -F, --freeze                    freeze row transaction information
      --force-index-cleanup       always remove index entries that point to dead tuples
  -j, --jobs=NUM                  use this many concurrent connections to vacuum
      --min-mxid-age=MXID_AGE     minimum multixact ID age of tables to vacuum
      --min-xid-age=XID_AGE       minimum transaction ID age of tables to vacuum
      --no-index-cleanup          don't remove index entries that point to dead tuples
      --no-process-toast          skip the TOAST table associated with the table to vacuum
      --no-truncate               don't truncate empty pages at the end of the table
  -P, --parallel=PARALLEL_WORKERS use this many background workers for vacuum, if available
  -q, --quiet                     don't write any messages
      --skip-locked               skip relations that cannot be immediately locked
  -t, --table='TABLE[(COLUMNS)]'  vacuum specific table(s) only
  -v, --verbose                   write a lot of output
  -V, --version                   output version information, then exit
  -z, --analyze                   update optimizer statistics
  -Z, --analyze-only              only update optimizer statistics; no vacuum
      --analyze-in-stages         only update optimizer statistics, in multiple stages for faster results; no vacuum
  -?, --help                      show this help, then exit

Connection options:
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as
  -w, --no-password         never prompt for password
  -W, --password            force password prompt
  --maintenance-db=DBNAME   alternate maintenance database


Related PostgreSQL Articles: pg_dumpall commands in PostgreSQL

May 20, 2021

PostgreSQL pg_restore utility

pg_restore - PostgreSQL database/backup restore tool


pg_restore
pg_restore --help
pg_restore --version

pg_restore -d=newdvdrental --verbose c:\pgbackup\dvdrental.tar
pg_restore --dbname=dvdrental --create -v c:\pgbackup\dvdrental.tar
pg_restore --dbname=dvdrental_tpl --section=pre-data /usr/pgbackup/dvdrental.tar

pg_restore -C -d postgres db.dump
pg_restore -L db.list db.dump
pg_restore -t emp -d testdb -U postgres testdb.dump
pg_restore binary_file.backup > sql_file.sql

pg_restore -Ft -d estore -U postgres estore.tar
pg_restore -Fd -l estore
pg_restore -Fd -C -d edb estore
pg_restore -Fc --no-acl --no-owner -U username -d database_name file_name.dump

pg_restore --help

Usage:
  pg_restore [OPTION]... [FILE]

General options:
  -d, --dbname=NAME       connect to database name
  -f, --file=FILENAME      output file name (- for stdout)
  -F, --format=c|d|t        backup file format (should be automatic)
  -l, --list                print summarized TOC of the archive
  -v, --verbose            verbose mode
  -V, --version            output version information, then exit
  -?, --help                show this help, then exit

Options controlling the restore:
  -a, --data-only              restore only the data, no schema
  -c, --clean                  clean (drop) database objects before recreating
  -C, --create                  create the target database
  -e, --exit-on-error          exit on error, default is to continue
  -I, --index=NAME            restore named index
  -j, --jobs=NUM                use this many parallel jobs to restore
  -L, --use-list=FILENAME      use table of contents from this file for selecting/ordering output
  -n, --schema=NAME            restore only objects in this schema
  -N, --exclude-schema=NAME do not restore objects in this schema
  -O, --no-owner                skip restoration of object ownership
  -P, --function=NAME(args)    restore named function
  -s, --schema-only            restore only the schema, no data
  -S, --superuser=NAME          superuser user name to use for disabling triggers
  -t, --table=NAME              restore named relation (table, view, etc.)
  -T, --trigger=NAME            restore named trigger
  -x, --no-privileges          skip restoration of access privileges (grant/revoke)
  -1, --single-transaction      restore as a single transaction
  --disable-triggers            disable triggers during data-only restore
  --enable-row-security        enable row security
  --if-exists                  use IF EXISTS when dropping objects
  --no-comments                do not restore comments
  --no-data-for-failed-tables  do not restore data of tables that could not be created
  --no-publications            do not restore publications
  --no-security-labels          do not restore security labels
  --no-subscriptions            do not restore subscriptions
  --no-table-access-method     do not restore table access methods
  --no-tablespaces              do not restore tablespace assignments
  --section=SECTION            restore named section (pre-data, data, or post-data)
  --strict-names                require table and/or schema include patterns to match at least one entity each
  --use-set-session-authorization use SET SESSION AUTHORIZATION commands instead of ALTER OWNER commands to set ownership

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password            force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before restore

The options -I, -n, -N, -P, -t, -T, and --section can be combined and specified multiple times to select multiple objects.


Related Postgres Articles: pglogical replication between Postgres Aurora Clusters   PostgreSQL pg_dump tool

May 14, 2021

PostgreSQL psql utility

psql   PostgreSQL interactive terminal

psql --help
U - Username
d - Database name
W - to force super user to provide password
h - Hostname
p - port number
f - file path

psql --version
psql testdb
psql -U postgres
psql -U postgres -d testdb

psql -p 5301
psql -p 5444 -U postgres
psql -p 5302 -U postgres -E
psql -p 5434 -U d3_user postgres
psql -p 5432 testdb -f /tmp/export.sql

psql -U postgres -f backupfile.sql
psql -U postgres --set ON_ERROR_STOP=on -f backupfile
psql -U postgres -d devdb -f objects.sql
psql -U postgres -d devdb < /home/pgsql/devdb_backup.sql
psql -h localhost -p 5301 -U test -d postgres

psql -p 5311 -c "SELECT max(distinct date(timestamp)) FROM audit_events" go_exnts
psql -c "CHECKPOINT"      -- For forceful checkpoint
psql --port=5432 --host=localhost --dbname=nw --username=postgres
psql -c "select * from orders" -t -o orders.txt store dbuser1


Related PostgreSQL Articles:  Postgres pg_ctl commands   pg_restore utility