PostgreSQL

file

-- environment variable PGDATA
-- /path/to/postgresql/data/pgdata
-- e.g. /var/lib/postgresql/data/pgdata

-- pg_default, default tablespace
-- $PGDATA/base

-- pg_global, system dictionary table
-- $PGDATA/global

-- PostgreSQL Client Authentication Configuration File
-- $PGDATA/pg_hba.conf

-- PostgreSQL User Name Maps
-- $PGDATA/pg_ident.conf

-- PostgreSQL configuration file
-- $PGDATA/postgresql.conf

tablespace

-- list for tablespace
-# \db[+]
-# CREATE TABLESPACE <tablespace-name> LOCATION <path-to-dir>;
-# CREATE TABLESPACE <tablespace-name> OWNER <username>;
-# CREATE TABLESPACE <tablespace-name> OPTIONS (seq_page_cost 1.0, random_page_cost 4.0, effective_io_concurrency 1, maintenance_io_concurrency 10);

-- seq_page_cost (floating point) - https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-SEQ-PAGE-COST
-- random_page_cost (floating point) - https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-RANDOM-PAGE-COST
-- effective_io_concurrency (integer) - https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-EFFECTIVE-IO-CONCURRENCY
-- maintenance_io_concurrency (integer) - https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAINTENANCE-IO-CONCURRENCY

-# ALTER DATABASE <database-name> SET TABLESPACE <tablespace-name>;
-# ALTER TABLE <table-name> SET TABLESPACE <table-name>;

-- temporary tablespace
-# SET temp_tablespaces=dir-path0,dir-path1;
-# SHOW temp_tablespaces;
-- postgresql.conf temp_tablespaces=dir-path0,dir-path1

drop

-# DROP DATABASE <database-name>;
-- DETAIL:  There is 1 other session using the database.
-- interrupt terminate session
-# SELECT pg_terminte_backend(pid) FROM pg_stat_activity WHERE datname='<database-name>';

use

-- psql is the PostgreSQL interactive terminal
$ psql -U [username]

-- tablespace



-- show databses
-# SELECT * FROM pg_database;
-- 
\list
\l
\+

table

-- like mysql AUTO_INCREMENT
-- SMALLSERIAL: 
-- SERIAL: 
-- BIGSERIAL: 
create table test(id serial primary key, name varchar(12));
insert into test(name) value ('a'), ('b');

backup

user

CREATE USER