Lets search for postgre images
E:\practices\docker\postgres>docker search postgres
NAME DESCRIPTION STARS OFFICIAL AUTOMATED
postgres The PostgreSQL object-relational database sy… 8009 [OK]
sameersbn/postgresql 153 [OK]
paintedfox/postgresql A docker image for running Postgresql. 77 [OK]
bitnami/postgresql Bitnami PostgreSQL Docker Image 58 [OK]
centos/postgresql-96-centos7 PostgreSQL is an advanced Object-Relational … 44
postgrest/postgrest REST API for any Postgres database 31
arm32v7/postgres The PostgreSQL object-relational database sy… 26
circleci/postgres The PostgreSQL object-relational database sy… 22
wrouesnel/postgres_exporter Postgres metrics exporter for Prometheus. 22
centos/postgresql-10-centos7 PostgreSQL is an advanced Object-Relational … 17
centos/postgresql-94-centos7 PostgreSQL is an advanced Object-Relational … 16
schickling/postgres-backup-s3 Backup PostgresSQL to S3 (supports periodic … 15 [OK]
postdock/postgres PostgreSQL server image, can work in master … 13 [OK]
debezium/postgres PostgreSQL for use with Debezium change data… 12 [OK]
clkao/postgres-plv8 Docker image for running PLV8 1.4 on Postgre… 12 [OK]
prodrigestivill/postgres-backup-local Backup PostgresSQL to local filesystem with … 10 [OK]
centos/postgresql-95-centos7 PostgreSQL is an advanced Object-Relational … 6
camptocamp/postgres Docker image for PostgreSQL including some e… 5 [OK]
jgiannuzzi/postgres-bdr Docker image for PostgreSQL with BDR support 4 [OK]
blacklabelops/postgres Postgres Image for Atlassian Applications 4 [OK]
dcm4che/postgres-dcm4chee PostgreSQL for dcm4che-arc 5.x 3 [OK]
ansibleplaybookbundle/postgresql-apb An APB which deploys RHSCL PostgreSQL 2 [OK]
fredboat/postgres PostgreSQL 10.0 used in FredBoat's docker-co… 1
manageiq/postgresql Container with PostgreSQL and built on CentO… 0 [OK]
cfcommunity/postgresql https://github.com/cloudfoundry-community/po… 0
E:\practices\docker\postgres>
Lets pull the latest image
E:\practices\docker\postgres>docker pull postgres
Using default tag: latest
latest: Pulling from library/postgres
Digest: sha256:ec7cfff29672a2f676c11cc53ae7dafe63a57ccefc2b06ea423493227da29b9c
Status: Image is up to date for postgres:latest
docker.io/library/postgres:latest
E:\practices\docker\postgres>
Using docker run
Run the docker image
E:\practices\docker\postgres>docker run -d --name pNode1 postgres:12 -e POSTGRES_PASSWORD=mysecretpassword -e POSTGRES_USER=postgress -e POSTGRES_DB=postgress -p 9432:5432
8c6ccb3f3089dd0d35ac4a437c42bab222b743dc2b0feefb11bd945c1801bfc3
Lets get the container details
E:\practices\docker\postgres>docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
8c6ccb3f3089 postgres:12 "docker-entrypoint.s…" 13 seconds ago Exited (4294967295) 9 seconds ago pNode1
Lets view the logs to see if there is any error, and indeed there are errors, and it seems it is a bug in image
E:\practices\docker\postgres>docker logs 8c6ccb3f3089
Error: Database is uninitialized and superuser password is not specified.
You must specify POSTGRES_PASSWORD to a non-empty value for the
superuser. For example, "-e POSTGRES_PASSWORD=password" on "docker run".
You may also use "POSTGRES_HOST_AUTH_METHOD=trust" to allow all
connections without a password. This is *not* recommended.
See PostgreSQL documentation about "trust":
https://www.postgresql.org/docs/current/auth-trust.html
E:\practices\docker\postgres>
Using Docker Compose
Since we cannot run docker images, let see if docker-compose works.
lets create db-data folder
docker-compose.yml
version: '3.6'
services:
pdb:
image: postgres
restart: always
environment:
POSTGRES_PASSWORD: postgrespassword
ports:
- 6543:5432
volumes:
- ./db-data:/var/lib/postgresql/data
adminer:
image: adminer
depends_on:
- pdb
restart: always
ports:
- 8080:8080
pgadmin:
image: dpage/pgadmin4
depends_on:
- pdb
ports:
- 7070:80
environment:
PGADMIN_DEFAULT_EMAIL: [email protected]
PGADMIN_DEFAULT_PASSWORD: admin
restart: unless-stopped
Lets run docker-compose
E:\practices\docker\postgres>docker-compose up -d
Creating network "postgres_default" with the default driver
Creating postgres_adminer_1 ... done Creating postgres_pdb_1 ... done
Creating postgres_pgadmin_1 ... done
E:\practices\docker\postgres>
Lets get container ids
E:\practices\docker\postgres>docker-compose ps
Name Command State Ports
------------------------------------------------------------------------------------
postgres_adminer_1 entrypoint.sh docker-php-e ... Up 0.0.0.0:8080->8080/tcp
postgres_db_1 docker-entrypoint.sh postgres Up 5432/tcp
Lets view the logs
E:\practices\docker\postgres>docker-compose logs
Attaching to postgres_adminer_1, postgres_db_1
db_1 | The files belonging to this database system will be owned by user "postgres".
db_1 | This user must also own the server process.
db_1 |
db_1 | The database cluster will be initialized with locale "en_US.utf8".
db_1 | The default database encoding has accordingly been set to "UTF8".
db_1 | The default text search configuration will be set to "english".
db_1 |
db_1 | Data page checksums are disabled.
db_1 |
db_1 | fixing permissions on existing directory /var/lib/postgresql/data ... ok
db_1 | creating subdirectories ... ok
db_1 | selecting dynamic shared memory implementation ... posix
db_1 | selecting default max_connections ... 100
db_1 | selecting default shared_buffers ... 128MB
db_1 | selecting default time zone ... Etc/UTC
db_1 | creating configuration files ... ok
db_1 | running bootstrap script ... ok
db_1 | performing post-bootstrap initialization ... ok
db_1 | syncing data to disk ... ok
db_1 |
db_1 |
db_1 | Success. You can now start the database server using:
db_1 |
db_1 | pg_ctl -D /var/lib/postgresql/data -l logfile start
db_1 |
db_1 | initdb: warning: enabling "trust" authentication for local connections
db_1 | You can change this by editing pg_hba.conf or using the option -A, or
db_1 | --auth-local and --auth-host, the next time you run initdb.
db_1 | waiting for server to start....2020-06-02 05:59:15.389 UTC [52] LOG: starting PostgreSQL 12.3 (Debian 12.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
db_1 | 2020-06-02 05:59:15.399 UTC [52] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
db_1 | 2020-06-02 05:59:15.483 UTC [52] LOG: could not resolve "localhost": Name or service not known
db_1 | 2020-06-02 05:59:15.483 UTC [52] LOG: disabling statistics collector for lack of working socket
db_1 | 2020-06-02 05:59:15.483 UTC [52] WARNING: autovacuum not started because of misconfiguration
db_1 | 2020-06-02 05:59:15.483 UTC [52] HINT: Enable the "track_counts" option.
db_1 | 2020-06-02 05:59:15.495 UTC [53] LOG: database system was shut down at 2020-06-02 05:59:10 UTC
db_1 | 2020-06-02 05:59:15.516 UTC [52] LOG: database system is ready to accept connections
db_1 | done
db_1 | server started
db_1 |
db_1 | /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
db_1 |
db_1 | 2020-06-02 05:59:15.582 UTC [52] LOG: received fast shutdown request
db_1 | waiting for server to shut down....2020-06-02 05:59:15.589 UTC [52] LOG: aborting any active transactions
db_1 | 2020-06-02 05:59:15.591 UTC [52] LOG: background worker "logical replication launcher" (PID 57) exited with exit code 1
db_1 | 2020-06-02 05:59:15.591 UTC [54] LOG: shutting down
db_1 | 2020-06-02 05:59:15.655 UTC [52] LOG: database system is shut down
db_1 | done
db_1 | server stopped
db_1 |
db_1 | PostgreSQL init process complete; ready for start up.
db_1 |
db_1 | 2020-06-02 05:59:15.713 UTC [1] LOG: starting PostgreSQL 12.3 (Debian 12.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
db_1 | 2020-06-02 05:59:15.713 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
db_1 | 2020-06-02 05:59:15.713 UTC [1] LOG: listening on IPv6 address "::", port 5432
db_1 | 2020-06-02 05:59:15.722 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
db_1 | 2020-06-02 05:59:15.804 UTC [1] LOG: could not resolve "localhost": Name or service not known
db_1 | 2020-06-02 05:59:15.804 UTC [1] LOG: disabling statistics collector for lack of working socket
db_1 | 2020-06-02 05:59:15.804 UTC [1] WARNING: autovacuum not started because of misconfiguration
db_1 | 2020-06-02 05:59:15.804 UTC [1] HINT: Enable the "track_counts" option.
db_1 | 2020-06-02 05:59:15.817 UTC [59] LOG: database system was shut down at 2020-06-02 05:59:15 UTC
db_1 | 2020-06-02 05:59:15.841 UTC [1] LOG: database system is ready to accept connections
adminer_1 | [Tue Jun 2 05:59:07 2020] PHP 7.4.6 Development Server (http://[::]:8080) started
E:\practices\docker\postgres>
Connecting to Postgres DB by getting into container
Lets connect to container
E:\practices\docker\postgres>docker-compose ps
Name Command State Ports
------------------------------------------------------------------------------------
postgres_adminer_1 entrypoint.sh docker-php-e ... Up 0.0.0.0:8080->8080/tcp
postgres_db_1 docker-entrypoint.sh postgres Up 5432/tcp
E:\practices\docker\postgres>docker exec -it postgres_db_1 bash
root@8f2771dd82ed:/#
change user
root@8f2771dd82ed:/# su postgres
postgres@8f2771dd82ed:/$
list the dbs
postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=#
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=#
Connecting to Postgres DB Adminer
lets connect to Aminer http://localhost:8080/
Connect To Postgres DB from local terminal
psql -h <host> -p <port> -U <user> -d <db>
C:\Users\nadeem>psql -h localhost -p 9090 -U postgres -d postgres
Password for user postgres:
psql (12.1, server 12.3 (Debian 12.3-1.pgdg100+1))
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page "Notes for Windows users" for details.
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=#
Connect to Postgres from DBeaver
Creating Database
postgres=# CREATE DATABASE testDb;
CREATE DATABASE
postgres=#
Create User
postgres-# \q
postgres@8f2771dd82ed:/$ createuser testuser -P --createdb
Enter password for new role:
Enter it again:
postgres@8f2771dd82ed:/$
postgres=# grant all privileges on database testdb to testuser;
GRANT
postgres=#
Doing purely via psql
CREATE DATABASE yourdbname;
CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';
GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;
Craftsman Nadeem 