PostgreSQL
- General PG admin (including creating new users, aka “roles”)
- Adding specific access (least privilege model)
- Optimization and Tuning
- Downloads
- PG transactions (xact_commit and xact_rollback)
- See config values:
psql -U postgres postgres -c "show all;" | grep cost - Version:
C:\>psql --version
psql (PostgreSQL) 10.11 - Locate main config file:
PGPASSWORD=fred psql -U escc postgres -c "show config_file;"
config_file
----------------------------------------
/var/lib/pgsql/10/data/postgresql.conf - Locate hba (host-based-authentication) file:
psql -U postgres
postgres=# show hba_file - List connections to a database:
Colin: “To see what the connections to the database are, can you run the following query against the cross-region database”:
psql -U postgres "MicroFocus\$CAS\$CrossRegion" -c "SELECT * FROM pg_stat_activity" - List databases - one-liner:
` psql -U postgres -c “select datname from pg_database”` - List databases, then quit:
psql -U postgres
\list
\q - List databaases from pg_database:
postgres=# select datname from pg_database; datname ------------------------------ postgres template1 template0 MicroFocus$CAS$CrossRegion MicroFocus$CAS$Region$MYPAC MicroFocus$SEE$Files$VSAM MicroFocus$CAS$Region$MYPAC2 (7 rows) - pgpass.conf:
Note: pgpass.conf simply provides the password for you.
C:\>type %APPDATA%\postgresql\pgpass.conf
localhost:5432:*:postgres:Passport1! - dump single database:
/usr/pgsql-10/bin/pg_dump -U escc -W -F t 'MicroFocus$SEE$Files$MLVSAM' > MLVSAM.tar - Restore single database dump:
NOTE: The pg_restore command is very confusing!!! If you are importing the backup archive into the same database, then you need to use “–dbname=postgres”. This is because the name of the database being restored is actually in the archive file. But, if you want to import the backup archive into a differently-named database, then you use “–dbname==", but you will have to create the database first yourself! See my script `pg_backup.sh` - Add a user
- Create a user with postgres privileges:
psql -U postgres postgres # \du # create user escc with encrypted password 'CC1adm1n'; # alter user escc with SUPERUSER CREATEDB REPLICATION;
- Create a user with postgres privileges:
- PG Replication status
- Vacuum and analyze (includes query to show last of both)
Written on October 13, 2020