Install
Postgresql orgnize data with 3-tiers structure: database > schema > table.
Install as user leo on Ubuntu 20.04:
$ sudo apt install postgresql
$ sudo su - postgres
postgres@wserver:~$ psql
postgres=# CREATE SCHEMA test;
CREATE SCHEMA
postgres=# CREATE USER leo PASSWORD 'zhangjingg';
CREATE ROLE
postgres=# GRANT ALL ON SCHEMA test TO leo;
GRANT
Now you can login to Postgresql database as user leo with psql -d postgres.
$ psql -d postgres
postgres=> \c
You are now connected to database "postgres" as user "leo".
Allow remote connection
$ sudo -u postgres psql -c 'SHOW config_file'
config_file
-----------------------------------------
/etc/postgresql/12/main/postgresql.conf
(1 row)
$ sudo vi /etc/postgresql/12/main/postgresql.conf
$ sudo vi /etc/postgresql/12/main/postgresql.conf
Replace #listen_addresses = 'localhost' with listen_addresses = '*' in file
/etc/postgresql/12/main/postgresql.conf.
And add a line host all all 192.168.1.0/24 md5 in section IPv4 local connections.
Then restart service: sudo systemctl restart postgresql.
Verify:
$ nc -zv 192.168.1.80 5432
Connection to 192.168.1.80 5432 port [tcp/postgresql] succeeded!
Use as a container
Install with container and connect to it:
podman run --name postgre -e POSTGRES_PASSWORD=postGREpwd -d postgres
podman exec -it postgre psql -U postgres
postgres=# \l # list databases
postgres=# create database mydb;
CREATE DATABASE
postgres=# \c
You are now connected to database "postgres" as user "postgres"
postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=# \d # notice the prompt of current database change to "mydb"
Did not find any relations.
mydb=# create table account (user_id serial primary key, username varchar (50) unique not null,
password varchar (50) not null, email varchar (255) unique not null
);
CREATE TABLE
mydb=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------------+----------+----------
public | account | table | postgres
public | account_user_id_seq | sequence | postgres
(2 rows)
mydb=# insert into account (username,password,email) values('leo', 'mypwd', 'leo@leo.me');
INSERT 0 1
mydb=# select * from account;
user_id | username | password | email
---------+----------+----------+------------
1 | leo | mypwd | leo@leo.me
(1 row)