selfhosted:psql [NetGeekery]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== PostgreSQL ======
    ## start
    $ psql

    ## open database
    $ psql --host=${host} --dbname=${database} --username=${user} --password

    ## exit
    ~$ postgres=# \q

====== Installation ======
    $ sudo apt-get install postgresql postgresql-contrib

====== Databases ======
    ## create
    ~$ postgres=# CREATE DATABASE ${database};

    ## list
    ~$ postgres=# \l

    ## URL
    ## DATABASE_URL=postgresql://${user}:${password}@postgres:5432/drkiq?encoding=utf8&pool=5&timeout=5000

    ## select database
    ~$ postgres=# \c ${database};

    ## backup
    PGPASSWORD="${password}" pg_dump -c -d ${database} -U ${user} -h localhost > dump_`date +%Y-%m-%d`.sql

    ## restore
    $ psql -d ${database} -f dump_`date +%Y-%m-%d`.sql
    $ psql -f db.out postgres

    ## save table to file
    ~$ postgres=# COPY checkins TO '/tmp/checkins_db.csv' DELIMITER ',' CSV HEADER;

====== Users ======
    ## create
    ~$ postgres=# CREATE USER ${user} WITH PASSWORD '${password}';

    ## grant privileges
    ~$ postgres=# GRANT ALL PRIVILEGES ON DATABASE ${database} to ${user};

    ## show users
    ~$ postgres=# \du

    ## rename user
    ~$ postgres=# ALTER USER ${user} RENAME TO ${new_user};

    ## change password
    ~$ postgres=# ALTER USER ${user} WITH PASSWORD '${new_pswd}';

    ## test
    $ psql --username=${user} --password

    ## forgot password
      $ sudo nano /etc/postgresql/13/main/pg_hba.conf
       < /etc/postgresql/13/main/pg_hba.conf >
        local all all                   trust
        host  all postgres 127.0.0.1/32 trust
        host  all all           ::1/128 trust
      $ sudo /etc/init.d/postgresql restart
      $ psql -h localhost -U postgres
      ~$ postgres=# ALTER USER postgres with password 'new_password';

    ## user authentication
    $ sudo nano /etc/postgresql/13/main/pg_hba.conf
        local all postgres         md5
        local all all              md5
        host  all all 127.0.0.1/32 md5
        host  all all ::1/128      md5

====== Setup ======
    ## change listen addresses
    $ sudo nano /etc/postgresql/13/main/postgresql.conf
        < etc/postgresql/13/main/postgresql.conf >
            listen_addresses = '*'

DOCKER AND PSQL:
    Starting an instance:
        # docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres
        $ docker run --name image_db_1 -e POSTGRES_PASSWORD=pg1234 -d --volumes-from postgres-data postgres
        # volumes-from: mount /var/lib/postgresql/data from postgres-data volume
        # docker run --name local-pg -p 5432:5432 -d postgres
        # docker run --name sw_db_backup -p 5432:5432 -d postgres:13

    Connect to it from an application:
        #$ docker run --name some-app --link some-postgres:postgres -d application-that-uses-postgres
    Connect to it via psql:
        #$ docker run -it --rm --link some-postgres:postgres postgres psql -h postgres -U postgres
        ! working !
        $ docker run -it --link image_db_1:postgres --net the_app --rm postgres:13 sh -c 'exec psql -h postgres -p 5432 -U postgres'

ERROR:
    # missing templates
    $ create database template0 TEMPLATE postgres;
    $ update pg_database set datistemplate=true  where datname='template0';

    Cannot link to image_db_1, as it does not belong to the default network
        $ docker network ls
            NETWORK ID          NAME                       DRIVER              SCOPE
            39da4dfdd2f1        bridge                     bridge              local
            1457d75d42a7        host                       host                local
            b30a51375e67        none                       null                local
            d7ca18692329        the_app                    bridge              local
            0836c4154d76        app_test                   bridge              local
        $ docker run -it --link image_db_1:postgres --net the_app  --rm postgres:13

    Open a shell prompt:
        ! working !
        $ docker ps
        CONTAINER ID    IMAGE                   COMMAND     CREATED         STATUS
        3c1add52fdad4   postgres:13         "docker-entrypoint.sh"  48 seconds ago  Up
        PORTS               NAMES
        0.0.0.0:5432->5432/tcp image_db_1
        $ docker exec -it 3c1add52fdad4 sh
        $ su - postgres
        $ psql
        $ \l
        $ \c prod_db
        $ \dt
    Deleting volumes:
        $ docker run --rm -v /var/lib/postgresql/data postgres:13

    =================================
    Data in db_1 is updated in real time.
    Data in pg_data_1 is updated after committing.
        $ docker ps
        CONTAINER ID    IMAGE                   COMMAND     CREATED         STATUS
        2d17d374f665    sw_db_image:v1      "docker-entrypoint.sh"  48 seconds ago  Up
        PORTS               NAMES
        5432/tcp image_db_1

        # make sure POSTGRES_PORT_5432_TCP_ADDR = postgres and POSTGRES_PORT_5432_TCP_PORT = 5432
        $ docker run -it --link image_db_1:postgres --rm postgres:13 sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres -d prod_db'
        $ docker run -it --link image_db_1:postgres --rm postgres:13 sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres -d prod_db'

        # list databases
        $ \l
        $ \c prod_db
        $ \dt
        $ select * from users;

        $ docker commit -m "message" 2d17d374f665 sw_db_image:v2


    Restart table index with 1:
        $ psql
        # get the sequence name
        $ SELECT pg_get_serial_sequence('users','id');
        pg_get_serial_sequence
        -----------------------
        public.users_id_seq

        $ ALTER SEQUENCE 'users_id_seq' RESTART WITH 1;

BACKUPS:
    # $ docker exec -t image_db_1 pg_dumpall -c -U postgres > dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql
    $ docker exec -t image_db_1 pg_dumpall -c -U postgres > ../_backups/dump_`date +%Y-%m-%d`.sql
    # restore
    $ docker start sw_db_backup
    $ cat dump_06-02-2017_15_55_49.sql | docker exec -i sw_db_backup psql -U postgres
    $ docker run -it --link sw_db_backup:postgres --rm postgres:13 sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres -d prod_db'

BACKUPS FROM HEROKU:
    DATABASE_URL=postgres://arpgybtfkjuqur:osiTmzUq8tlscexAz6M_eaIb3N@ec2-54-243-200-159.compute-1.amazonaws.com:5432/dcjgjfffk4vdc0
    # download the sql dump
    $ pg_dump --host=ec2-54-243-200-159.compute-1.amazonaws.com --port=5432 --username=arpgybtfkjuqur --password --dbname=dcjgjfffk4vdc0 > output.sql
    password: osiTmzUq8tlscexAz6M_eaIb3N

RESTORE FROM HEROKU:
    $ cat output.sql | docker exec -i image_db_1 psql -U postgres -d prod_db
    $ docker ps
    CONTAINER ID    IMAGE                   COMMAND     CREATED         STATUS
    9e4f0fc31113    postgres:13         "docker-entrypoint.sh"  48 seconds ago  Up
    PORTS               NAMES
    0.0.0.0:5432->5432/tcp image_db_1
    $ docker exec -it 9e4f0fc31113 sh
    $ su - postgres
    $ psql
    $ \dt


OTHER COMMANDS:
    # adding password
    $ CREATE USER user WITH PASSWORD 'user_password';

    # adding privileges
    $ GRANT ALL PRIVILEGES ON DATABASE db to user;

    # revoking privileges
    $ REVOKE ALL PRIVILEGES ON DATABASE db FROM user;

    # deleting user
    $ DROP USER user;

    # delete row
    $ DELETE FROM table1 WHERE name = 'something';

    # create database
    $ CREATE DATABASE db_name

    # delete database
    # change a value
    $ UPDATE pg_database SET datistemplate='false' WHERE datname='template0';
        UPDATE 1
    $ DROP DATABASE template0;
        DROP DATABASE

    # superuser privileges
        # common errors:
            # - must be owner of extension plpgsql
            # - must be owner of schema public
    $ ALTER ROLE user SUPERUSER;

    # change table owenership
    $ ALTER TABLE table OWNER TO user;

    # change password
    $ \password user

    # sort database
    psql > select * from my_table where user = 'Username1' order by ID desc limit 2.
    
    # update a column for all rows in table
    psql > update "table_name" set col_name = 'val';

=========================
Updating PostgresSQL version:
    $ sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main"
    $ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
    $ sudo apt-get update
    $ sudo apt-get install postgresql-13

Changing postgresql port:
    $ sudo nano /etc/postgresql/13/main/postgresql.conf

Changing pg_dump version:
    $ find / -name pg_dump -type f 2>/dev/null
        /usr/lib/postgresql/13/bin/pg_dump

Backup:
  # $ PGPASSWORD="db_password" /usr/lib/postgresql/13/bin/pg_dump --host=xxxxx.us-east-1.rds.amazonaws.com --port=5432 --username=user1 --dbname=db1 > /home/user1/Documents/_backups/db1/db_dump_`date +%Y_%m_%d`.sql
  ssh xxx@xxxxxxx "PGPASSWORD="db_password" /usr/lib/postgresql/13/bin/pg_dump \
  -h xxxxx.us-east-1.rds.amazonaws.com -p 5432 -U user1 \
  -F c -b -v -f "/home/user1/Documents/_backups/db1/db_dump_`date +%Y_%m_%d`.sql" db1"

Restore
  # psql -d db_data1 -f db_dump_`date +%Y_%m_%d`.sql
  # pg_restore -d db1 -U user1 -C /home/user1/Documents/_backups/db1/db_dump_`date +%Y_%m_%d`.sql
  PGPASSWORD="db_password" pg_restore --clean --host=localhost --dbname=db1 --username=user1 /home/user1/Documents/_backups/db1/db_dump_`date +%Y_%m_%d`.sql

count:
    psql: select count(*) from table;

query:
    SELECT column_name, count(*) from public.relation_name GROUP BY column_name ORDER BY column_name;
    example:
        SELECT program, COUNT(*) FROM public.all_hotels GROUP BY program ORDER BY program;
    example:
        SELECT * from table_name where column_name = 'some_value';

columns:
    psql: \d+ table_name;

    ## update value at ${column} in ${table}
    $ update ${table} set ${column}=md5('${new_value}') where ${column}='${some_value}';

options:
    # expanded display
    \x on

misc:
    # clear table (and reset index)
    truncate table "table_name" restart identity cascade;