Working with Databases

CREATE DATABASE shop;
CREATE DATABASE shop
  WITH OWNER = app_owner
       ENCODING = 'UTF8'
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE   = 'en_US.UTF-8'
       LOCALE_PROVIDER = 'icu' ICU_LOCALE = 'en-US'
       TEMPLATE = template0
       CONNECTION LIMIT = 200;

\l                  -- psql shortcut
\l+                 -- include sizes
SELECT datname, pg_catalog.pg_get_userbyid(datdba) AS owner,
       pg_size_pretty(pg_database_size(datname)) AS size
  FROM pg_database ORDER BY datname;

\c shop
\c shop alice
psql -d shop

ALTER DATABASE shop OWNER TO app_owner;

CREATE DATABASE shop
  LC_COLLATE = 'en_US.UTF-8'
  LC_CTYPE   = 'en_US.UTF-8'
  TEMPLATE   = template0;

ALTER DATABASE shop RENAME TO shop_old;

ALTER DATABASE shop SET work_mem = '64MB';
ALTER DATABASE shop SET search_path = app, public;
ALTER DATABASE shop RESET work_mem;

\l+ shop
SELECT * FROM pg_database WHERE datname = 'shop';
SELECT datname, datcollate, datctype, encoding, datconnlimit
  FROM pg_database WHERE datname = 'shop';

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

DROP DATABASE IF EXISTS shop_old;
DROP DATABASE shop WITH (FORCE);   -- terminates other sessions (PG 13+)