Postgresql tips and links


  • Backups

    • barman https://sourceforge.net/projects/pgbarman/files/2.3/
      • Needs an additionnal server (atqbk1?)
      • check_barman https://github.com/hamann/check-barman
      • has a puppet module
    • pgbackrest (favorite amongst people in the PosgreSQL slack community)
      • check_pgbackrest https://labs.dalibo.com/check_pgbackrest
      • No puppet module
    • pg_probackup (https://github.com/postgrespro/pg_probackup
    • https://github.com/aiven/pghoard
    • pitrery: PITR made easy https://github.com/dalibo/pitrery
    • New in version 13: pg_verifybackup

  • Testing


  • Monitoring

    • PANIC , ERROR or WARNING in logs
      • TailNMail: detect interesting messages in logs and send by email: https://bucardo.org/tail_n_mail/ 
    • pghero
    • PMM from Percona - Free and opensource!, for MySQL, MariaDB and PostgreSQL and others
      • https://www.percona.com/doc/percona-monitoring-and-management/conf-postgres.html 
      • https://www.percona.com/blog/2019/08/30/pmm-for-postgresql-quick-start-guide/ 
      • https://www.percona.com/blog/2018/05/08/deploying-pmm-at-linode-your-5-per-month-monitoring-solution/
    • https://github.com/cybertec-postgresql/pgwatch2 
    • pgbadger: It’s a software that performs an analysis of PostgreSQL logs and displays them in an HTML file. It helps you to understand the behavior of your database and identify which queries need to be optimized. 
    • PostgreSQL exporters for Prometheus: https://github.com/prometheus-community/postgres_exporter
    • Available in the PG upstream repo: http://pgcluu.darold.net/
    • https://github.com/CrunchyData/pgmonitor
    • pgdash (paid) 
    • https://vector.dev/ (open-source, made by datadog)
    • temboard https://temboard.io/
    • pg_stat_monitor
    • Extensions to help:
      • pg_stat_statements: This extension will help you know the query profile of your database. It tracks all the queries that are executed and stores a lot of useful information in a table called pg_stat_statements. By querying this table you can get what queries are run in the system, how many times they have run, and how much time they have consumed, among other information.

      • pgstattuple: It can generate statistics for tables and indexes, showing how much space used by each table and index, is consumed by live tuples, deleted tuples or how much-unused space is available in each relation.
      • pg_buffercache: With this, you can check what's happening in the shared buffer cache in real-time, showing how many pages are currently held in the cache.

  •  Optimization

  • EXPLAIN PLAN GUIs 
    • Web-based:
      • https://tatiyants.com/pev/#/plans
      • https://explain.depesz.com/
      • https://explain.dalibo.com/ 
      • https://dalibo.github.io/pev2/#/
      •  https://www.pgmustard.com/ (not free)
    • Apps:
      • Local java app fot EXPLAIN plans:  https://github.com/wmeitzen/postgresql-explain-graph
      • pg_flame
  • Index optimization tips: https://pganalyze.com/index-adviso

  • Misc, ungrouped stuff

  • https://pgloader.io/
  • Rebuild an inconsistent slave:
    • https://severalnines.com/database-blog/how-rebuild-inconsistent-postgresql-slave
  • In-depth explanation about PostgreSQL locks
    • https://www.highgo.ca/2020/02/21/have-an-eye-on-locks-of-postgresql/
  • Quick commands to know database size:
    • select pg_size_pretty(pg_database_size('database_name')); 
    • in psql:
      • \l+ databasename
      • \l+ (for all databases on this cluster)
  • Handy cheat sheet: https://postgrescheatsheet.com/
  • Enterprise support/consulting:
    • Crunchy Data
    • Severalnines
    • 2ndQuadrant
    • Percona
    • Cybertec
    • EnterpriseDB
  • Backup and restore tutorial: https://postgresql.r2schools.com/how-to-take-backup-and-restore-a-postgresql-database/
  • Very interesting blog post about logging: https://richyen.com/postgres/2020/01/29/underestimating_log.html
  • 10 tools for pgsql development/management: https://www.enterprisedb.com/postgres-tutorials/10-tools-every-developer-should-have-when-working-postgresql
  • What NOT to do in PostgreSQL:  https://wiki.postgresql.org/wiki/Don%27t_Do_This
    • SQL tests to see if your database follows rules from pgsql wiki: https://gitlab.com/depesz/pgWikiDont
  • Understanding and managing replication slots: https://severalnines.com/database-blog/using-postgresql-replication-slots
  • Duplicate databases: https://www.cybertec-postgresql.com/en/tips-and-tricks-to-kick-start-postgres-year-2020/ (use template)
  • Best postgresql.conf documentation: https://postgresqlco.nf/en/doc/param/
  • Security features in PostgreSQL: https://www.highgo.ca/2020/01/22/understanding-security-features-in-postgresql-part-1/ (3 parts)
  • schema change management:
    • https://sqitch.org/about/ 
    • https://github.com/djrobstep/migra
  • Mount options (Summary - not really needed. May give 1% performance increase)
    • mount -t xfs -o noatime,nodiratime,logbsize=256k -o allocsize=1m,nobarrier /dev/VG_POSTGRES/LV_DATA /db (https://subscription.packtpub.com/book/big_data_and_business_intelligence/9781849516969/8/ch08lvl1sec108/tweaking-xfs-performance)
    • This article says only nobarrier is necessary: https://www.percona.com/blog/2018/07/03/linux-os-tuning-for-mysql-database-performance/
      • " For XFS filesystems the default atime behavior is relatime, which has almost no overhead compared to noatime and still maintains sane atime values."
  • pgsql job scheduler: https://dba.stackexchange.com/a/151093 
  • psql -E show actual SQL request (useful to see that special commands like \xd do)
  • Explanations of types of locks: https://www.percona.com/blog/2018/10/24/postgresql-locking-part-2-heavyweight-locks/
  • Sharding: Being able to insert rows into a remote partition is new in version 11. With this feature, you can now have your data sharded logically (partitions) and physically (FDW). https://pgdash.io/blog/postgres-11-sharding.html
  • pg_top: https://severalnines.com/database-blog/dynamic-monitoring-postgresql-instances-using-pgtop
  • How to move a tablespace: https://momjian.us/main/blogs/pgblog/2018.html#October_3_2018
  • Check jit compilation. default = off in 11. I don't know in 12
  • Evaluate changing md5 for scram-sha-256 
    • http://hacksoclock.blogspot.com/2018/10/how-to-set-up-scram-sha-256.html
  •  Tuning pgsql and Linux kernel parameters for performance:
    •  https://www.percona.com/blog/2018/08/31/tuning-postgresql-database-parameters-to-optimize-performance/
    • https://www.percona.com/blog/2018/08/29/tune-linux-kernel-parameters-for-postgresql-optimization/
  • Database security webinar https://www.2ndquadrant.com/en/blog/webinar-database-security-postgresql/
  •  Very interesting documentation about administering pgsql systems: https://momjian.us/main/writings/pgsql/administration.pdf
  • prewarm - autoprewarm (warms cache on a standby) https://postgresrocks.enterprisedb.com/t5/Postgres-Gems/Autoprewarm-a-new-functionality-in-pg-prewarm/ba-p/1908
  • Backups, logical vs physical
    • Logical not really good for more than 50-100 GB DBs
  • Record and replay (workload simulation)?
    • https://wiki.postgresql.org/wiki/Statement_Playback
  • Check cache hit ratio (should be 99%)
  • Top resources for performance monitoring: https://severalnines.com/database-blog/performance-monitoring-auditing-postgresql-top-resources
  • CIS benchmark: https://www.cisecurity.org/benchmark/postgresql/
  • Understanding pgsql internal catalog (pg_stats, pg_locks, etc.)
  • pg_verify_checksums 
    • https://thebuild.com/blog/2018/07/17/that-google-checksum-tool/
  • How pgsql manages UPDATEs and works with autovaccum, and why it is sometimes better to convert to using INSERTS: https://www.cybertec-postgresql.com/en/a-beginners-guide-to-postgresqls-update-and-autovacuum/
    https://tapoueh.org/blog/2018/07/modeling-for-concurrency/
  • Integrating tools to manage pgsql in prod: https://severalnines.com/database-blog/integrating-tools-manage-postgresql-production
  • Interesting feature: async notifications: https://citizen428.net/blog/asynchronous-notifications-in-postgres/
  • Using Huge Pages: https://rjuju.github.io/postgresql/2018/07/03/diagnostic-of-unexpected-slowdown.html
  • Tuning i/o: https://severalnines.com/blog/tuning-io-operations-postgresql
  • Basic performance troubleshooting
    •  https://severalnines.com/database-blog/performance-cheat-sheet-postgresql
    • https://severalnines.com/blog/new-webinar-introduction-performance-monitoring-postgresql
  • pgsql backups: https://hackernoon.com/elephant-in-the-room-database-backup-574da50e6d88
  • Connections: apparently, performance is not good on pgsql for above 350 connections w/o pgbouncer
  • Multi-datacenter postgresql: https://severalnines.com/database-blog/multi-datacenter-setups-postgresql
  • Troubleshoot and fix replication:
    • Logical https://medium.com/avitotech/recovery-use-cases-for-logical-replication-in-postgresql-10-a1e6bab03072 
    • Streaming: https://info.crunchydata.com/blog/wheres-my-replica-troubleshooting-streaming-replication-synchronization-in-postgresql
  • Managing memory in pgsql: https://www.citusdata.com/blog/2018/06/12/configuring-work-mem-on-postgres/https://severalnines.com/database-blog/architecture-and-tuning-memory-postgresql-databases
  • PHP persistent connections settings: https://www.php.net/manual/en/function.pg-pconnect.php
  • Streaming replication types explained https://blog.timescale.com/blog/scalable-postgresql-high-availability-read-scalability-streaming-replication-fb95023e2af/
  •  https://github.com/dbacvetkov/PASH-Viewer
  • pgbadger, a must-have to generate on-demand graphs: http://pgbadger.darold.net/
    https://severalnines.com/database-blog/postgresql-log-analysis-pgbadger
  • Monitoring replication
    • https://www.scalingpostgres.com/tutorials/postgresql-replication-monitoring/
  • data type: Array
    • https://tapoueh.org/blog/2018/04/postgresql-data-types-arrays/
  • tools when vacuum is not enough
    • http://blog.dataegret.com/2018/03/postgresql-bloatbusters.html
  •  Configuring streaming replication: https://www.scalingpostgres.com/tutorials/postgresql-streaming-replication/
  • Streaming replication failover and failback
    • https://www.scalingpostgres.com/tutorials/postgresql-replication-easy-failback/
  • pgrewind (roll back transactions in the past)
    • https://www.scalingpostgres.com/tutorials/postgresql-replication-failback-pg-rewind/ 
    • https://thebuild.com/blog/2018/08/09/three-steps-to-pg_rewind-happiness/
  • Most modified pgsql config parameters
    • http://www.databasesoup.com/2018/04/new-annotated-config-files-for.html
  • Mass import tips
    • https://www.postgresql.org/docs/current/static/populate.html
  • Monitoring/Troubleshooting VACUUM/AutoVACUUM
    • https://www.datadoghq.com/blog/postgresql-vacuum-monitoring/ 
    • https://blog.gojekengineering.com/postgres-autovacuum-tuning-394bb99fe2c0 
    • autovacuum can be tuned per table: https://www.percona.com/blog/2018/08/29/tune-linux-kernel-parameters-for-postgresql-optimization/
  • enable wal_compression
    • They say it's a no-brainer unless you have serious CPU performance issues
  • Almost no downtime upgrade with logical replication https://blog.2ndquadrant.com/near-zero-downtime-automated-upgrades-postgresql-clusters-cloud/
  • Logical replication technical notes https://severalnines.com/blog/overview-logical-replication-postgresql
  • High availability solutions for PG https://severalnines.com/blog/top-pg-clustering-ha-solutions-postgresql
  • https://pgmetrics.io/: support bundle for pgsql
  • Vertical scaling https://pgdash.io/blog/scaling-postgres.html
  • https://www.cybertec-postgresql.com/en/detecting-performance-problems-easily-in-postgresql/
    • shows how to install pg_stat_statements and queries to troubleshoot
  • https://severalnines.com/database-blog/key-things-monitor-postgresql-analyzing-your-workload 
  • Slow request detection/troubleshooting:
    •  https://www.cybertec-postgresql.com/en/3-ways-to-detect-slow-queries-in-postgresql/ 
    • ttps://severalnines.com/database-blog/postgresql-running-slow-tips-tricks-get-source
    • http://postgresguide.com/
    •  https://www.cybertec-postgresql.com/en/setting-postgresql-configuration-parameters/
    • https://www.cybertec-postgresql.com/
    • Do not execute initdb until you have a lot of RAM allocated to the VM
    • https://users.cs.duke.edu/~shivnath/papers/ituned.pdf
    • rpm -ql contrib
    • https://severalnines.com/blog/become-postgresql-dba-understanding-architecture
    • https://www.revsys.com/writings/postgresql-performance.html
    • https://amplitude.engineering/how-a-single-postgresql-config-change-improved-slow-query-performance-by-50x-85593b8991b0
      • Read comments
    • https://github.com/jfcoz/postgresqltuner
      • Needs yum install perl-DBD-Pg
    • PostgreSQL puppet module
    • Activer postgresql driver dans Zend Server via Puppet
    • https://www.scalingpostgres.com/episodes/3-modeling-query-performance-statistics-pgmetrics/
      • Other entries on this blog
    • contribs package
    • Performance Co-Pilot PostgreSQL (pcp-pmda-postgresql.x86_64)
    • List of interesting packages:
      • rh-postgresql96-postgresql-contrib-syspaths
      • phpPgAdmin
      • pagila? - a sample database for postgresql
      • pg_top
      • pg_view
      • pgadmin3
      • pgadmin4 and https://www.postgresql.org/docs/9.1/adminpack.html
      • pgcenter
      • https://www.pgconfig.org/
      • pgtune, web based https://pgtune.leopard.in.ua/
      •  https://pgconfigurator.cybertec-postgresql.com/
      • pgtune -i /var/lib/pgsql/data/postgresql.conf -o /tmp/yes.conf; diff  /var/lib/pgsql/data/postgresql.conf /tmp/yes.conf
      • postgis?
      • rhdb-utils
      • nagios-plugins-pgsql
    • https://access.redhat.com/documentation/en-us/reference_architectures/2017/html/red_hat_cloudforms_4.1x_-_implementing_a_highly_available_virtual_management_database/postgresql_configuration
    • Parameters
      • shared_buffers
      • work_mem
    • Kernel tuning
      • open files
      • hugepages
    • Where to put WAL files?
    • Do we archive WAL files?
      • Where to put archived WAL files
    • SELinux contexts
      • semanage fcontext -l | grep postgres | sort -k 4
    • createuser --interactive
    • Performance stats
      • pgadmin4
      • pcp-pmda-postgresql
    • Huge pages
      • THP can be bad, just as with Oracle
    • Recommendation
      • Enable data checksums
        • 1-2% performance hit
        • Must be done when creating the cluster (initdb)
        • Off by default on RHEL7
    • Logging
      • checkpoints
      • connections
      • disconnections
      • queries?
      • DDL
        • Add informations, like username (log_line_prefix)
    • Autovacuum is not very aggressive by default, should be tuned
      • Causes more IO than necessary, because empty pages are still read
    • effective_io_concurrency
      • Default=1
      • Worth upping
      • pgtune recommends 300 for a SAN
    • force_parallel_mode
      • Forces use of parallel query facilities.
    • SSL?
    • Useful scripts
      • https://www.dbrnd.com/postgresql-dba-scripts/
    • Nagios: 
      • Make sure we use the most recent check_postgres nagios plugin 
      • Other Nagios plugin that looks promising: 
        • https://exchange.nagios.org/directory/Plugins/Databases/PostgresQL/check_pgactivity/details 
        • https://exchange.nagios.org/directory/Plugins/Databases/PostgresQL/check_pg_streaming_replication/details


    check_postgres.pl --action=archive_ready
    POSTGRES_ARCHIVE_READY OK: DB "postgres" WAL ".ready" files found: 0 | time=0.08s files=0;10;15

    check_postgres.pl --action=autovac_freeze
    POSTGRES_AUTOVAC_FREEZE OK: DB "postgres"  postgres=0%;90;95  template1=0%;90;95 | time=0.08s postgres=0%;90;95  template1=0%;90;95

    check_postgres.pl --action=backends
    POSTGRES_BACKENDS OK: DB "postgres" 2 of 100 connections (2%) | time=0.08s postgres=2;90;95;0;100 template0=0;90;95;0;100 template1=0;90;95;0;100

    check_postgres.pl --action=bloat --db=test1
    POSTGRES_BLOAT OK: DB "test1" (db test1) index pg_depend_reference_index rows:? pages:45 shouldbe:33 (1.4X) wasted bytes:98304 (96 kB) | pg_depend_reference_index=98304B pg_catalog.pg_class=32768B pg_catalog.pg_description=16384B pg_amop_fam_strat_index=8192B pg_amop_opr_fam_index=8192B pg_catalog.pg_amop=8192B pg_catalog.pg_conversion=8192B pg_catalog.pg_operator=8192B pg_catalog.pg_depend=0B pg_class_oid_index=0B pg_class_relname_nsp_index=0B pg_class_tblspc_relfilenode_index=0B pg_conversion_oid_index=0B pg_description_o_c_o_index=0B pg_operator_oid_index=0B pg_operator_oprname_l_r_n_index=0B

    --checkpoint not working :(

    check_postgres.pl --action=commitratio --db=test1
    POSTGRES_COMMITRATIO OK: DB "test1" template1: 94.44 test1: 100.00 postgres: 100.00  | time=0.09s template1=94.44; test1=100.00;; postgres=100.00;

    check_postgres.pl --action=database_size -w 1G -c 2G
    POSTGRES_DATABASE_SIZE OK: DB "postgres" postgres: 7151788 (6984 kB) template0: 7029252 (6865 kB) template1: 7029252 (6865 kB)  | time=0.08s postgres=7151788;1073741824;2147483648 template0=7029252;1073741824;2147483648 template1=7029252;1073741824;2147483648

    CACTI only: check_postgres.pl --action=dbstats --db=test1
    backends:1 commits:33 rollbacks:0 read:235 hit:5234 idxscan:0 idxtupread:0 idxtupfetch:0 idxblksread:0 idxblkshit:0 seqscan:0 seqtupread:0 ret:21209 fetch:2939 ins:0 upd:0 del:0 dbname:test1

    check_postgres.pl --action=hitratio --db=test1
    POSTGRES_HITRATIO OK: DB "test1" template1: 96.34 test1: 96.44 postgres: 99.99  | time=0.08s template1=96.34; test1=96.44;; postgres=99.99;

    check_postgres.pl --action=indexes_size --db=test1 -w 1G -c 2G
    POSTGRES_INDEXES_SIZE OK: DB "test1" table with largest indexes is "pg_catalog.pg_depend": 680 kB | time=0.08s pg_catalog.pg_statistic=16384B;1073741824;2147483648 pg_catalog.pg_type=57344B;1073741824;2147483648 pg_toast_2604=8192B;1073741824;2147483648 pg_toast_2606=8192B;1073741824;2147483648 pg_toast_2609=8192B;1073741824;2147483648 pg_toast_1255=8192B;1073741824;2147483648 pg_toast_2618=16384B;1073741824;2147483648 pg_toast_3596=8192B;1073741824;2147483648 pg_toast_2619=16384B;1073741824;2147483648 pg_toast_2620=8192B;1073741824;2147483648 pg_toast_2396=8192B;1073741824;2147483648 pg_toast_2964=8192B;1073741824;2147483648 pg_toast_3592=8192B;1073741824;2147483648 pg_catalog.pg_authid=32768B;1073741824;2147483648 pg_catalog.pg_user_mapping=16384B;1073741824;2147483648 pg_catalog.pg_largeobject=8192B;1073741824;2147483648 pg_catalog.pg_attribute=188416B;1073741824;2147483648 pg_catalog.pg_proc=327680B;1073741824;2147483648 pg_catalog.pg_class=73728B;1073741824;2147483648 pg_catalog.pg_attrdef=16384B;1073741824;2147483648 pg_catalog.pg_constraint=65536B;1073741824;2147483648 pg_catalog.pg_inherits=16384B;1073741824;2147483648 pg_catalog.pg_index=32768B;1073741824;2147483648 pg_catalog.pg_operator=81920B;1073741824;2147483648 pg_catalog.pg_opfamily=32768B;1073741824;2147483648 pg_catalog.pg_opclass=32768B;1073741824;2147483648 pg_catalog.pg_am=32768B;1073741824;2147483648 pg_catalog.pg_amop=114688B;1073741824;2147483648 pg_catalog.pg_amproc=73728B;1073741824;2147483648 pg_catalog.pg_language=32768B;1073741824;2147483648 pg_catalog.pg_largeobject_metadata=8192B;1073741824;2147483648 pg_catalog.pg_aggregate=16384B;1073741824;2147483648 pg_catalog.pg_rewrite=32768B;1073741824;2147483648 pg_catalog.pg_trigger=24576B;1073741824;2147483648 pg_catalog.pg_event_trigger=16384B;1073741824;2147483648 pg_catalog.pg_description=172032B;1073741824;2147483648 pg_catalog.pg_cast=32768B;1073741824;2147483648 pg_catalog.pg_enum=24576B;1073741824;2147483648 pg_catalog.pg_namespace=32768B;1073741824;2147483648 pg_catalog.pg_conversion=49152B;1073741824;2147483648 pg_catalog.pg_depend=696320B;1073741824;2147483648 pg_catalog.pg_db_role_setting=8192B;1073741824;2147483648 pg_catalog.pg_tablespace=32768B;1073741824;2147483648 pg_catalog.pg_pltemplate=16384B;1073741824;2147483648 pg_catalog.pg_auth_members=16384B;1073741824;2147483648 pg_catalog.pg_shdepend=32768B;1073741824;2147483648 pg_catalog.pg_shdescription=16384B;1073741824;2147483648 pg_catalog.pg_ts_config=32768B;1073741824;2147483648 pg_catalog.pg_ts_config_map=32768B;1073741824;2147483648 pg_catalog.pg_ts_dict=32768B;1073741824;2147483648 pg_catalog.pg_ts_parser=32768B;1073741824;2147483648 pg_catalog.pg_ts_template=32768B;1073741824;2147483648 pg_catalog.pg_extension=32768B;1073741824;2147483648 pg_catalog.pg_foreign_data_wrapper=16384B;1073741824;2147483648 pg_catalog.pg_foreign_server=16384B;1073741824;2147483648 pg_catalog.pg_foreign_table=8192B;1073741824;2147483648 pg_catalog.pg_policy=16384B;1073741824;2147483648 pg_catalog.pg_replication_origin=16384B;1073741824;2147483648 pg_catalog.pg_default_acl=16384B;1073741824;2147483648 pg_catalog.pg_seclabel=8192B;1073741824;2147483648 pg_catalog.pg_shseclabel=8192B;1073741824;2147483648 pg_catalog.pg_collation=32768B;1073741824;2147483648 pg_catalog.pg_range=16384B;1073741824;2147483648 pg_catalog.pg_transform=16384B;1073741824;2147483648 pg_catalog.pg_database=32768B;1073741824;2147483648 pg_toast_12252=8192B;1073741824;2147483648 information_schema.sql_features=0B;1073741824;2147483648 pg_toast_12257=8192B;1073741824;2147483648 information_schema.sql_implementation_info=0B;1073741824;2147483648 pg_toast_12262=8192B;1073741824;2147483648 information_schema.sql_languages=0B;1073741824;2147483648 pg_toast_12267=8192B;1073741824;2147483648 information_schema.sql_packages=0B;1073741824;2147483648 pg_toast_12272=8192B;1073741824;2147483648 information_schema.sql_parts=0B;1073741824;2147483648 pg_toast_12277=8192B;1073741824;2147483648 information_schema.sql_sizing=0B;1073741824;2147483648 pg_toast_12282=8192B;1073741824;2147483648 information_schema.sql_sizing_profiles=0B;1073741824;2147483648

    check_postgres.pl --action=locks --db=test1
    POSTGRES_LOCKS OK: DB "test1" total=1  | time=0.08s postgres.total=0;100;150 template1.total=0;100;150 test1.total=1;100;150

    check_postgres.pl --action=query_time --db=test1 -w 0.05 -c 0.2
    POSTGRES_QUERY_TIME OK: DB "test1" longest query: 0s  | time=0.07s query_time=0s;0.05;0.2

    check_postgres.pl --action=timesync --db=test1 -w 1 -c 2
    POSTGRES_TIMESYNC OK: DB "test1" timediff=0 DB=2018-05-31 15:34:11 Local=2018-05-31 15:34:11 | time=0.08s diff=0s;1;2

    check_postgres.pl --action=txn_idle --db=test1 -w 1 -c 2
    POSTGRES_TXN_IDLE OK: DB "test1" no idle in transaction | time=0.08s transaction_time=0;1;2

    check_postgres.pl --action=txn_time --db=test1 -w 1 -c 2
    POSTGRES_TXN_TIME OK: DB "test1" longest txn: 0s  | time=0.07s transaction_time=0s;1;2

    check_postgres.pl --action=wal_files --db=test1
    POSTGRES_WAL_FILES OK: DB "test1" WAL files found: 1 | time=0.07s files=1;10;15


    Comments

    Post a Comment

    Popular posts from this blog

    General linux performance troubleshooting

    Here's a list of commands that you should execute and then share the output to someone who can help you figure out what resource is the bottleneck in your system. Note, it requires the  sysstat  and  procps  packages (ubuntu and RHEL and its derivatives): uptime vmstat 1 10 iostat -xN 2 10 mpstat -P ALL 3 10 pidstat 1 10 free -mw (or free -m, if your OS doesn't support -w)  uptime is to see the load averages on the system. vmstat is mostly used to tell if the system is swapping or not. If you see significant numbers in the 'si' and 'so' columns, your system is most likely swapping (using the hard drive as RAM), which usually slows performance a lot. iostat is mostly used to determine if your disk subsystem is not able to cope with the load. If you see one or more lines that shows 100 or close almost constantly, it is probably the case.  If it is your swap volume, you probably saw numbers in the 'si' and 'so' columns in the  vmsta...
    Read more

    Networker automated recovery testing using the REST API - introduction

    One feature that is lacking from Networker, compared to some of its competitors, is the built-in automatic recovery testing. However, when there's an API, there is a way.  Networker's REST API is not perfect, but it allows the backup administrator to perform queries about Networker resources (objects). As my workload is going up, I realize that one of the tasks that I tend to skip the most is the periodical recovery tests.  Don't forget that a a backup that is not tested should be considered non-successful. I also found out that my recovery tests were not diverse enough. When I started this project, I knew a little bit about REST APIs, and nothing about JSON processing. With the Networker REST API documentation, and the help of a friend and Networker Support staff, I was able to create HTTP queries with Postman,  cURL and jq . Once I got the queries that I needed, I put them in a bash script that would somehow select one backup, and then restore it. My first attem...
    Read more

    Tips for being a better system administrator

    Here are a few tips that I have discovered or implemented during my career, that can help anyone get better results: Follow the 3-2-1 backup strategy  or better 3 copies of the data 3 different media 1 copy offsite Test your backups regularly. Ideally, automate your recovery tests. Use Whatever-as-code as much as you can. IaC is the first that comes to my mind, but using code to define objects and their properties have several advantages: Auto-documentation of changes Ability to easily rollback (using source code management like Git) Makes it easier for standardization an compliance Ansible, Chef, Puppet, Salt, Terraform, Pulumi are good examples Maintain a changelog of all the major changes in your infrastructure (that isn't already "documented" because you're using IaC). Keep track of your hardware inventory and plan your renewals and warranty extension purchases. Build a spreadsheet with all the components that you manage and make sure that everyone on the sysadmi...
    Read more