Learn more
PostgreSQL is a robust database management system (DBMS) with a strong emphasis on extensibility and SQL compliance. Boasting 20 years of open-source development, it supports both SQL (relational) and JSON (non-relational) querying. Widely used in web, mobile, and analytics applications, PostgreSQL is a powerful choice for managing your data.
Update package information before installation:
sudo apt update
Install PostgreSQL along with necessary extensions:
sudo apt install postgresql postgresql-contrib
Verify PostgreSQL status:
service postgresql status
The output should confirm that the PostgreSQL daemon is active.
Log in as the default admin user "postgres":
sudo -u postgres psql
This also connects you to the default database named "postgres."
Check the connection details:
\conninfo
You should see that you are connected to the "postgres" database as user "postgres."
To check the PostgreSQL version:
postgres --version
or
postgres -V
View a list of available databases:
\l
If you want to get more information, you can use the \l+ command:
\l+
Set a password for the default "postgres" user:
\password postgres
Create users with specific roles and permissions:
sudo -u postgres createuser -e project_udev sudo -u postgres createuser -d -r -e project_utest sudo -u postgres createuser --superuser project_ulive
Alternatively, use the CREATE USER PSQL statement:
CREATE USER project_ulive SUPERUSER;
List all users and roles:
\du
Use PSQL to create a user with a password:
CREATE USER project_ulive WITH PASSWORD 'Live@Project123';
If the user already exists, add the password by using ALTER USER:
ALTER USER project_udev WITH PASSWORD 'Dev@Project123';
Create databases:
CREATE DATABASE project_dev; CREATE DATABASE project_test; CREATE DATABASE project_prod;
Grant permissions:
GRANT CONNECT ON DATABASE project_dev TO project_udev; GRANT ALL PRIVILEGES ON DATABASE project_dev to project_udev;
Allow remote access in the PostgreSQL configuration files:
Edit postgresql.conf:
vim /etc/postgresql/14/main/postgresql.conf
Uncomment and edit the listen_addresses attribute to allow all IP addresses.
Edit pg_hba.conf:
vim /etc/postgresql/14/main/pg_hba.conf
Append a new connection policy.
Restart PostgreSQL:
systemctl restart postgresql
Confirm listening on port 5432:
ss -nlt | grep 5432
Connect remotely using psql:
psql -U ghealth_udev -p 5432 -h 10.10.18.231
This refined version maintains the structure of your guide while making it more readable and reducing the likelihood of errors.