Postgres Commands

brew install postgresql

sudo apt-get install -y postgresql14

psql - start postgres sql query engine

psql -h xx.xx.us-west-2.rds.amazonaws.com -p 5432 -U postgres
xxx

psql -h xx.xx.xx.xx -p 5432 -U dev -d dev_metta_pg

psql -d postgres -U postgres
xx

\d - show databases
\d rides; - show rides table
select count(*) from rides;
\dx - List of installed extensions
\l - list all databases with owner
\dt
\dt+  - you get a view of the tables in the database along with an (empty) description column
\dn+ - public schema indicates permissions for the postgres role
\du - show all users
\dt *.* - show all tables

select version();
SELECT schema_name FROM information_schema.schemata;

create user myuser with encrypted password 'mypass';
grant all privileges on database postgres to myuser;
psql -h xx.xx.us-west-2.rds.amazonaws.com -p 5432 -U myuser -d postgres

CREATE USER quicksight4 PASSWORD 'ac9c922490fad8cafa5d68a5';
grant all privileges on database postgres to quicksight4;
grant all privileges on database prod_metta_pg2 to quicksight4;
psql -h xx.xx.us-west-2.rds.amazonaws.com -p 5432 -U quicksight4 -d postgres

GRANT CONNECT ON DATABASE postgres TO quicksight;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO quicksight;
GRANT USAGE ON SCHEMA public TO quicksight;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO quicksight;

REASSIGN OWNED BY quicksight TO postgres;  -- or some other trusted role
DROP OWNED BY ryan;
DROP USER ryan;

psql has a ECHO_HIDDEN variable you can set to show (or 'echo') any SQL queries performed behind the scenes by backslash commands.

set ECHO_HIDDEN on

-- select database
\c [databasename]: Connect to [databasename] on local database cluster

CREATE DATABASE zenalytix_db_new;

CREATE TABLE test ( id SERIAL PRIMARY KEY, create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, update_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, value INT );

Drop table test;

psql -U abc -d zenalytix_db_new -p 5432

psql -h localhost -p 5432 -U postgres -d airflow
psql -h localhost -p 5432 -U postgres (sentry)

-- get table sizes
SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a order by total_bytes desc;

-- get databases sizes
SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS SIZE
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first
    LIMIT 20;

-- table sizes
select table_name, pg_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 2;

REINDEX DATABASE zenalytx_db_new;
REINDEX INDEX index_name;
REINDEX TABLE table_name;

VACUUM numbers;
VACUUM FULL;  # to free up space in all the dbs

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

-- User Management
CREATE USER test WITH SUPERUSER PASSWORD 'test123';
ALTER USER test WITH PASSWORD 'test1234';
DROP USER test;

delete from task_instance where execution_date::date < '2021-01-13 21:00:00+0';

Administration

-- Postgres 14 adds the predefined, non-login roles [**`pg_read_all_data`** / **`pg_write_all_data`**](https://www.postgresql.org/docs/current/predefined-roles.html).
-- They have `SELECT` / `INSERT`, `UPDATE`, `DELETE` privileges for _all_ tables, views, and sequences. Plus `USAGE` on schemas. We can `GRANT` membership in these roles:

GRANT pg_read_all_data TO my_user;
GRANT pg_write_all_data TO my_user;
-- [sql - PostgreSQL: Give all permissions to a user on a PostgreSQL database - Stack Overflow](https://stackoverflow.com/questions/22483555/postgresql-give-all-permissions-to-a-user-on-a-postgresql-database)

REVOKE ALL PRIVILEGES ON DATABASE loantape FROM app_quicksight;
drop user app_quicksight;

set password_encryption = 'md5';

Configurations

show statement_timeout;
set statement_timeout to 60000; commit;

Tools

pg_buffercache

see what's occupying the shared buffer cache of your instance
pg_prewarm

load table data into either the operating system cache or the Postgres buffer cache

Commands

ABORT - abort the current transaction

ALTER AGGREGATE - change the definition of an aggregate function

ALTER COLLATION - change the definition of a collation

ALTER CONVERSION - change the definition of a conversion

ALTER DATABASE - change a database

ALTER DEFAULT PRIVILEGES - define default access privileges

ALTER DOMAIN - change the definition of a domain

ALTER EVENT TRIGGER - change the definition of an event trigger

ALTER EXTENSION - change the definition of an extension

ALTER FOREIGN DATA WRAPPER - change the definition of a foreign-data wrapper

ALTER FOREIGN TABLE - change the definition of a foreign table

ALTER FUNCTION - change the definition of a function

ALTER GROUP - change role name or membership

ALTER INDEX - change the definition of an index

ALTER LANGUAGE - change the definition of a procedural language

ALTER LARGE OBJECT - change the definition of a large object

ALTER MATERIALIZED VIEW - change the definition of a materialized view

ALTER OPERATOR - change the definition of an operator

ALTER OPERATOR CLASS - change the definition of an operator class

ALTER OPERATOR FAMILY - change the definition of an operator family

ALTER POLICY - change the definition of a row level security policy

ALTER PROCEDURE - change the definition of a procedure

ALTER PUBLICATION - change the definition of a publication

ALTER ROLE - change a database role

ALTER ROUTINE - change the definition of a routine

ALTER RULE - change the definition of a rule

ALTER SCHEMA - change the definition of a schema

ALTER SEQUENCE - change the definition of a sequence generator

ALTER SERVER - change the definition of a foreign server

ALTER STATISTICS - change the definition of an extended statistics object

ALTER SUBSCRIPTION - change the definition of a subscription

ALTER SYSTEM - change a server configuration parameter

ALTER TABLE - change the definition of a table

ALTER TABLESPACE - change the definition of a tablespace

ALTER TEXT SEARCH CONFIGURATION - change the definition of a text search configuration

ALTER TEXT SEARCH DICTIONARY - change the definition of a text search dictionary

ALTER TEXT SEARCH PARSER - change the definition of a text search parser

ALTER TEXT SEARCH TEMPLATE - change the definition of a text search template

ALTER TRIGGER - change the definition of a trigger

ALTER TYPE - change the definition of a type

ALTER USER - change a database role

ALTER USER MAPPING - change the definition of a user mapping

ALTER VIEW - change the definition of a view

ANALYZE - collect statistics about a database

BEGIN - start a transaction block

CALL - invoke a procedure

CHECKPOINT - force a write-ahead log checkpoint

CLOSE - close a cursor

CLUSTER - cluster a table according to an index

COMMENT - define or change the comment of an object

COMMIT - commit the current transaction

COMMIT PREPARED - commit a transaction that was earlier prepared for two-phase commit

COPY - copy data between a file and a table

CREATE ACCESS METHOD - define a new access method

CREATE AGGREGATE - define a new aggregate function

CREATE CAST - define a new cast

CREATE COLLATION - define a new collation

CREATE CONVERSION - define a new encoding conversion

CREATE DATABASE - create a new database

CREATE DOMAIN - define a new domain

CREATE EVENT TRIGGER - define a new event trigger

CREATE EXTENSION - install an extension

CREATE FOREIGN DATA WRAPPER - define a new foreign-data wrapper

CREATE FOREIGN TABLE - define a new foreign table

CREATE FUNCTION - define a new function

CREATE GROUP - define a new database role

CREATE INDEX - define a new index

CREATE LANGUAGE - define a new procedural language

CREATE MATERIALIZED VIEW - define a new materialized view

CREATE OPERATOR - define a new operator

CREATE OPERATOR CLASS - define a new operator class

CREATE OPERATOR FAMILY - define a new operator family

CREATE POLICY - define a new row level security policy for a table

CREATE PROCEDURE - define a new procedure

CREATE PUBLICATION - define a new publication

CREATE ROLE - define a new database role

CREATE RULE - define a new rewrite rule

CREATE SCHEMA - define a new schema

CREATE SEQUENCE - define a new sequence generator

CREATE SERVER - define a new foreign server

CREATE STATISTICS - define extended statistics

CREATE SUBSCRIPTION - define a new subscription

CREATE TABLE - define a new table

CREATE TABLE AS - define a new table from the results of a query

CREATE TABLESPACE - define a new tablespace

CREATE TEXT SEARCH CONFIGURATION - define a new text search configuration

CREATE TEXT SEARCH DICTIONARY - define a new text search dictionary

CREATE TEXT SEARCH PARSER - define a new text search parser

CREATE TEXT SEARCH TEMPLATE - define a new text search template

CREATE TRANSFORM - define a new transform

CREATE TRIGGER - define a new trigger

CREATE TYPE - define a new data type

CREATE USER - define a new database role

CREATE USER MAPPING - define a new mapping of a user to a foreign server

CREATE VIEW - define a new view

DEALLOCATE - deallocate a prepared statement

DECLARE - define a cursor

DELETE - delete rows of a table

DISCARD - discard session state

DO - execute an anonymous code block

DROP ACCESS METHOD - remove an access method

DROP AGGREGATE - remove an aggregate function

DROP CAST - remove a cast

DROP COLLATION - remove a collation

DROP CONVERSION - remove a conversion

DROP DATABASE - remove a database

DROP DOMAIN - remove a domain

DROP EVENT TRIGGER - remove an event trigger

DROP EXTENSION - remove an extension

DROP FOREIGN DATA WRAPPER - remove a foreign-data wrapper

DROP FOREIGN TABLE - remove a foreign table

DROP FUNCTION - remove a function

DROP GROUP - remove a database role

DROP INDEX - remove an index

DROP LANGUAGE - remove a procedural language

DROP MATERIALIZED VIEW - remove a materialized view

DROP OPERATOR - remove an operator

DROP OPERATOR CLASS - remove an operator class

DROP OPERATOR FAMILY - remove an operator family

DROP OWNED - remove database objects owned by a database role

DROP POLICY - remove a row level security policy from a table

DROP PROCEDURE - remove a procedure

DROP PUBLICATION - remove a publication

DROP ROLE - remove a database role

DROP ROUTINE - remove a routine

DROP RULE - remove a rewrite rule

DROP SCHEMA - remove a schema

DROP SEQUENCE - remove a sequence

DROP SERVER - remove a foreign server descriptor

DROP STATISTICS - remove extended statistics

DROP SUBSCRIPTION - remove a subscription

DROP TABLE - remove a table

DROP TABLESPACE - remove a tablespace

DROP TEXT SEARCH CONFIGURATION - remove a text search configuration

DROP TEXT SEARCH DICTIONARY - remove a text search dictionary

DROP TEXT SEARCH PARSER - remove a text search parser

DROP TEXT SEARCH TEMPLATE - remove a text search template

DROP TRANSFORM - remove a transform

DROP TRIGGER - remove a trigger

DROP TYPE - remove a data type

DROP USER - remove a database role

DROP USER MAPPING - remove a user mapping for a foreign server

DROP VIEW - remove a view

END - commit the current transaction

EXECUTE - execute a prepared statement

EXPLAIN - show the execution plan of a statement

FETCH - retrieve rows from a query using a cursor

GRANT - define access privileges

IMPORT FOREIGN SCHEMA - import table definitions from a foreign server

INSERT - create new rows in a table

LISTEN - listen for a notification

LOAD - load a shared library file

LOCK - lock a table

MOVE - position a cursor

NOTIFY - generate a notification

PREPARE - prepare a statement for execution

PREPARE TRANSACTION - prepare the current transaction for two-phase commit

REASSIGN OWNED - change the ownership of database objects owned by a database role

REFRESH MATERIALIZED VIEW - replace the contents of a materialized view

REINDEX - rebuild indexes

RELEASE SAVEPOINT - destroy a previously defined savepoint

RESET - restore the value of a run-time parameter to the default value

REVOKE - remove access privileges

ROLLBACK - abort the current transaction

ROLLBACK PREPARED - cancel a transaction that was earlier prepared for two-phase commit

ROLLBACK TO SAVEPOINT - roll back to a savepoint

SAVEPOINT - define a new savepoint within the current transaction

SECURITY LABEL - define or change a security label applied to an object

SELECT - retrieve rows from a table or view

SELECT INTO - define a new table from the results of a query

SET - change a run-time parameter

SET CONSTRAINTS - set constraint check timing for the current transaction

SET ROLE - set the current user identifier of the current session

SET SESSION AUTHORIZATION - set the session user identifier and the current user identifier of the current session

SET TRANSACTION - set the characteristics of the current transaction

SHOW - show the value of a run-time parameter

START TRANSACTION - start a transaction block

TRUNCATE - empty a table or set of tables

UNLISTEN - stop listening for a notification

UPDATE - update rows of a table

VACUUM - garbage-collect and optionally analyze a database

VALUES - compute a set of rows

https://www.postgresql.org/docs/current/sql-commands.html

pg_dumpall

pg_dumpall dumps all databases in given PostgreSQL installation (cluster), and does it to plain text file. Everything goes there. Additionally, it dumpsglobalthings -- roles and tablespaces, which cannot be dumped by pg_dump.

The major benefit of pg_dumpall is that it's single command, and you get results.

There is huge number of drawbacks though:

dump is large, because it's uncompressed
dumping is slow, because it's done sequentially, with single worker
it's hard to restore just parts of dump

pg_dump

pg_dump, on the other hand, can't dump globals, and can dump only one database at a time. But it can use four dump formats:

plain
custom
directory
tar

Plain is just plain text format, just like pg_dumpall dumps. You can load it with psql, and extracting parts can be complicated if dump is large.

All other formats (custom, directory, and tar) are restored using pg_restore program.

pg_basebackup

Used for physical backups

Adding hstore in database

psql -U postgres template1
create extension hstore;
q

Cannot use --keepdb after this, since new db is to be created

https://www.depesz.com/2019/12/10/how-to-effectively-dump-postgresql-databases

psql - start postgres sql query engine​

Administration​

Configurations​

Tools​

Commands​

pg_dumpall​

pg_dump​

pg_basebackup​

Adding hstore in database​