Using System Catalogs to Investigate a PostgreSQL Server

• Change to the postgres user:

  sudo su - postgres
  

• Create the database to be restored:

  createdb acweb
  

• Download the backup from GitHub:

  wget https://github.com/linuxacademy/content-postgresql-deepdive/raw/master/acweb/acweb.tar
  

• Use pg_restore to restore the backup:

  pg_restore --dbname=acweb --verbose /var/lib/pgsql/acweb.tar
  

• Launch psql and verify the data is present:

  psql
  

• List the databases with \l.
• Connect to acweb:

\c acweb

• Get a count for the payment table:

SELECT COUNT(*) FROM sales.payment;

• Exit psql:

\q

• Download a script to generate load against the database:

  wget https://raw.githubusercontent.com/linuxacademy/content-postgresql-deepdive/master/acweb/loadgen.sh
  

• Modify the file to be executable:

  chmod +x loadgen.sh
  

• Execute the script in the background:

  ./loadgen.sh > /dev/null 2>&1 &
  

psql
\x

SELECT pid, datname, usename, application_name, client_addr, xact_start, wait_event_type, wait_event, state, query
FROM pg_stat_activity WHERE application_name = 'psql';

\c acweb

SELECT pg_size_pretty(pg_database_size(current_database()));

SELECT table_schema, table_name, pg_size_pretty(pg_relation_size('"'||table_schema||'"."'||table_name||'"')) AS size
FROM information_schema.tables
WHERE table_schema = 'sales'
ORDER BY size DESC;

SELECT columns.attname as name,
 data_types.typname as type,
 class.relname as table,
 tables.schemaname as schema
FROM pg_attribute columns
INNER JOIN pg_class class ON columns.attrelid = class.oid
INNER JOIN pg_tables tables on class.relname = tables.tablename
INNER JOIN pg_type data_types 
ON columns.atttypid = data_types.oid
WHERE tables.schemaname = 'sales'
AND data_types.typname = 'varchar';