Postgresql Docker database

postgresdkdelete.md

Delete database in Postgresql running in Docker

    docker exec -it <container-id> psql -U <username> -d postgres -c "DROP DATABASE <dbname>;"

Create database in Postgresql running in Docker

    docker exec -it <container-id> psql -U <username> -d postgres -c "CREATE DATABASE <dbname>;"

Backup database in Postgresql running in Docker

• Only Data

    docker exec -it <container-id> pg_dump -U <username> --column-inserts --data-only <dbname> > <filename>.sql

    Example:

    docker exec -it f33c1990b211 pg_dump -U postgres --column-inserts --data-only app > backup.sql

• Database Data and tables in sql

    docker exec -it <container-id> pg_dump -U <username> --column-inserts <dbname> > <filename>.sql

    Example:

    docker exec -it f33c1990b211 pg_dump -U postgres --column-inserts  app > backup.sql

Execute sql file database in Postgresql running in Docker

• Database Data and tables in sql
  • First
    • Copy file into the container

    docker cp ./<filename_source>.sql <container_name>:/<filename_Destination>.sql
    
    Example:

    docker cp ./data.sql f33c1990b211:/data_in.sql

• After
  • Execute file into the container

    docker exec -it <container_name> psql -U <username> -a <db_name> -f /<filename_in_container>.sql

    Example:

    docker exec -it f33c1990b211 psql -U postgres -a app -f /data_in.sql

Get inside the container with a shell and then run psql and do whatever you want inside the psql command line.

    docker exec -it <container-id> /bin/sh

Run psql directly in docker

    docker exec -it <container-id> psql -U <username> -d <database-name>

have psql installed locally and run it to access the postgres instance in docker

    psql -U <username> -h localhost

Ensure the tables were created:

    docker-compose exec db psql --username=<username> --dbname=<database-name>

List the database Example

$ docker-compose exec db psql --username=hello_django --dbname=hello_django_dev

psql (12.0)
Type "help" for help.

hello_django_dev=# \l
                                          List of databases
       Name       |    Owner     | Encoding |  Collate   |   Ctype    |       Access privileges
------------------+--------------+----------+------------+------------+-------------------------------
 hello_django_dev | hello_django | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres         | hello_django | UTF8     | en_US.utf8 | en_US.utf8 |
 template0        | hello_django | UTF8     | en_US.utf8 | en_US.utf8 | =c/hello_django              +
                  |              |          |            |            | hello_django=CTc/hello_django
 template1        | hello_django | UTF8     | en_US.utf8 | en_US.utf8 | =c/hello_django              +
                  |              |          |            |            | hello_django=CTc/hello_django

Connect to the DB - list tables and quit

    hello_django_dev=# \c hello_django_dev
You are now connected to database "hello_django_dev" as user "hello_django".

hello_django_dev=# \dt
                     List of relations
 Schema |            Name            | Type  |    Owner
--------+----------------------------+-------+--------------
 public | auth_group                 | table | hello_django
 public | auth_group_permissions     | table | hello_django
 public | auth_permission            | table | hello_django
 public | auth_user                  | table | hello_django
 public | auth_user_groups           | table | hello_django
 public | auth_user_user_permissions | table | hello_django
 public | django_admin_log           | table | hello_django
 public | django_content_type        | table | hello_django
 public | django_migrations          | table | hello_django
 public | django_session             | table | hello_django
(10 rows)

hello_django_dev=# \q

ssh_tunnel.md

Create ssh file to connect witn tunnel to DigitalOcean

By default newer versions of ssh-keygen generate key in RFC4716 format, but PGAdmin supports only PEM. So you need to add -m PEM option when generating your key.

    ssh-keygen -m PEM -t rsa -b 4096