PostgreSQL

Command Line Tools

Queries

• Create an Admin user from command line

CREATE USER user WITH LOGIN PASSWORD 'md53Oa21Oc89654a4b3a8255688186I58a9' VALID UNTIL '2205-01-01' SUPERUSER ;

• Get list of databases and their sizes:

SELECT pg_database.datname, pg_database_size(pg_database.datname), pg_size_pretty(pg_database_size(pg_database.datname)) 
FROM pg_database ORDER BY pg_database_size DESC;

Shell

• Connect to a PostgreSQL server.

export PGPASSWORD="password"
psql -t -A -h <hostname> <database> <utente> -c "SELECT myquery FROM table";

• script to compare the row count on all tables between a master and a replica database (run in the replica with "trusted" user)

tables=`$PCOMMAND -c "select tablename from pg_tables;"`

host_src=10.1.1.1
host_dst=10.1.1.2
PCOMMAND="psql -t -A  -Upostgres db_name"

tables=`$PCOMMAND -h$host_src -c "SELECT * FROM information_schema.tables WHERE table_type != 'VIEW'"|awk -F"|" '{print $2"."$3}'`

IFS=$'\n'
for TABLE in $tables ;do
    unset IFS
    count_src=`$PCOMMAND -h$host_src -c "select count(*) from $TABLE;"`
    count_dst=`$PCOMMAND -h$host_dst -c "select count(*) from $TABLE;"`
    if [ $count_src -ne $count_dst ];then
      echo -e "different record count betwen $host_src ($count_src)\t and $host_dst ($count_dst)\t in table $TABLE"
    fi
done

Configuration

• some standard locations

  • home:/var/lib/postgres
  • conf:/etc/postgres/x.x/main/
  • logs:/var/log/postgres/x.x/main

• More Logging

add in postgresql.conf

log_line_prefix = '%t %c %u ' # time sessionid user
log_statement = 'all'

Errors

2025-02-16 16:05:08.491 UTC 67b20cb4.1fdcb 0 archiver archiver FATAL:  password authentication failed for user "archiver"
2025-02-16 16:05:08.491 UTC 67b20cb4.1fdcb 0 archiver archiver DETAIL:  User "archiver" has no password assigned.
        Connection matched pg_hba.conf line 7: "local all all md5"
2025-02-16 16:05:08.491 UTC 67b20cb4.1fdcb 0 archiver archiver LOG:  could not send data to client: Broken pipe
2025-02-16 16:05:08.510 UTC 67b20cb4.1fdd0 0 [unknown] archiver ERROR:  replication slot "vpg_archiver" already exists
2025-02-16 16:05:08.510 UTC 67b20cb4.1fdd0 0 [unknown] archiver STATEMENT:  CREATE_REPLICATION_SLOT "vpg_archiver" PHYSICAL
2025-02-16 16:05:08.817 UTC 67b20cb4.1fdd8 0 [unknown] archiver ERROR:  requested WAL segment 0000000100000022000000C4 has already been removed
2025-02-16 16:05:08.817 UTC 67b20cb4.1fdd8 0 [unknown] archiver STATEMENT:  START_REPLICATION SLOT "vpg_archiver" 22/C4000000 TIMELINE 1


PostgreSQL GUI's
phpPgAdmin
*; fix problem with Postgres version 9.4
* taken and modified from: http://stackoverflow.com/questions/13467949/error-on-creating-database-in-phppgadmin
Quick Fix: (worked with my Version (5.0.3) / pg 9.2.3 )

#cd classes/database
#copy Postgres84.php to Postgres94.php
#open Connection.php
#add a line  case '9.4': return 'Postgres94'; break; at the // Detect version and choose appropriate database driver switch.
#open Postgres.php and copy functions getTablespaces + getTablespace
#open Postgres94.php and paste the functions into the class
## replace ", spclocation," with ", pg_tablespace_location(oid) as spclocation," in both functions.
## change class name to Postgres94

PostgreSQLStudio
http://www.postgresqlstudio.org/download/

TeamPostgreSQL
http://www.teampostgresql.com/