PSQL Survival Commands
Table of contents
Inside Psql
List DB
\l
Change DB
\c dbname
List extensions
\dx
Execute a file
psql --file create_db_with_user.sql
Execute a file with arguments
psql --file create_db_with_user.sql -v db="dbname"
# Note the ' this is necessary to interpret as string
psql --file create_db_with_user.sql -v some_string="'password'"
No access?
By default postgres only allow access using "ident" mode or "peer" mode (unix user)
su postgres
psql
Edit pg_hba.conf
to allow access by password
pg_hba="/var/lib/pgsql/12/data/pg_hba.conf"
cp -r $pg_hba "${pg_hba}.bak"
sed -i '/^host[ ]*all[ ]*all[ ]*127.0.0.1\/32[ ]*ident/ s/ident/md5/' $pg_hba
sed -i '/^host[ ]*all[ ]*all[ ]*::1\/128[ ]*ident/ s/ident/md5/' $pg_hba
Create a new db and a user for it
su - postgres
createdb dbname
psql -d dbname
CREATE USER username pasword 'userpwd';
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;
Now you can user other user
psql -U username -d dbname -h localhost
Access DB from inside Docker Container
Edit pg_hba.confg
find / -name "pg_hba.confg.conf" -exec vi {} \;
Find docker network bridge info:
docker network inspect bridge
And change IPv4 to
host all all 172.17.0.0/8 md5
According to
docker network inspect bridge
CIDR should be /16 but actually only /8 works.
Edit postgresql.conf
find / -name "postgresql.conf" -exec vi {} \;
And change listen_addresses
to docker's gateway inet
listen_addresses = 'localhost,172.17.0.1'
Restart pgsql
systemctl restart postgresql-12
Did you find this article valuable?
Support mwrites by becoming a sponsor. Any amount is appreciated!