PostgreSQL is one of the most powerful open-source relational database systems used for modern web applications, enterprise platforms, SaaS products, microservices, analytics systems, and production-grade backend services. If you are working with Laravel, Django, Node.js, Java, Python, or any cloud-native application, PostgreSQL is a very strong choice because of its reliability, data integrity, security, indexing, JSON support, and scalability.
pgAdmin is the most popular web-based GUI tool for managing PostgreSQL. It allows developers and administrators to create databases, manage users, run SQL queries, inspect tables, monitor servers, and troubleshoot database connections from a browser.
In this guide, we will install PostgreSQL and pgAdmin4 Web on Ubuntu, create database users, configure application databases, connect PostgreSQL with Laravel, secure pgAdmin, test database passwords, and upgrade PostgreSQL from version 14 to 16.
Important Security Note:
Never publish real database passwords, pgAdmin passwords, or production credentials in blogs, GitHub, screenshots, or documentation. Always use placeholders like StrongPasswordHere.
Step 1: Update Ubuntu Packages
Before installing PostgreSQL, always update the system package index. This ensures that Ubuntu knows about the latest available packages.
sudo apt update
You can also upgrade installed packages if required:
sudo apt upgrade -y
This step is important because many installation issues happen due to outdated package lists.
Step 2: Install PostgreSQL on Ubuntu
Now install PostgreSQL and the additional contrib package. The postgresql-contrib package provides extra utilities and extensions that are commonly useful in production environments.
sudo apt install postgresql postgresql-contrib -y
After installation, enable PostgreSQL so it starts automatically after server reboot:
sudo systemctl enable --now postgresql
Check the PostgreSQL service status:
sudo systemctl status postgresql
If everything is correct, you should see PostgreSQL as active (exited) or active (running) depending on your Ubuntu/PostgreSQL setup.
Step 3: Check PostgreSQL Version
To check the installed PostgreSQL client version, run:
psql --version
Example output:
psql (PostgreSQL) 14.23
However, this only shows the psql client version. To check the actual running PostgreSQL server version, use:
sudo -u postgres psql -c "SHOW server_version;"
Or:
sudo -u postgres psql -c "SELECT version();"
This confirms the real PostgreSQL server version.
Step 4: Understand PostgreSQL Default Admin User
PostgreSQL does not have a default root user like MySQL. Instead, PostgreSQL creates a default Linux and database user named:
postgres
This postgres user is the default PostgreSQL admin/superuser.
To login as the PostgreSQL admin user:
sudo -u postgres psql
You will enter the PostgreSQL shell:
postgres=#
To exit PostgreSQL shell:
\q
Step 5: Create PostgreSQL Root/Superuser
In some cases, you may want to create a PostgreSQL user named root with superuser privileges. This is optional.
Login as PostgreSQL admin:
sudo -u postgres psql
Create a root superuser:
CREATE ROLE root WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'StrongRootPasswordHere';
Exit:
\q
Test login:
psql -h 127.0.0.1 -U root -d postgres -W
Enter the password when prompted.
Security Note
Do not use root or any superuser account in your application .env. Superuser accounts should only be used for administration.
Step 6: Create a Separate PostgreSQL User for Application
For production applications, always create a separate database user. Your Laravel, Django, Node.js, or other application should never connect using a superuser.
Login to PostgreSQL:
sudo -u postgres psql
Create an application user:
CREATE USER holiday_user WITH ENCRYPTED PASSWORD 'StrongAppPasswordHere';
Create a database:
CREATE DATABASE holiday_db OWNER holiday_user;
Grant privileges:
GRANT ALL PRIVILEGES ON DATABASE holiday_db TO holiday_user;
Exit:
\q
Test login:
psql -h 127.0.0.1 -U holiday_user -d holiday_db -W
If login succeeds, you will see:
holiday_db=>
Exit:
\q
Step 7: Install Required Packages for pgAdmin4
pgAdmin4 Web requires some system dependencies such as curl, gnupg, and ca-certificates.
Install them using:
sudo apt update
sudo apt install curl ca-certificates gnupg lsb-release -y
These packages are needed to add pgAdmin’s official repository securely.
Step 8: Add pgAdmin4 Repository
Add pgAdmin’s package signing key:
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
Now add the pgAdmin repository:
echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" | sudo tee /etc/apt/sources.list.d/pgadmin4.list
Update package list:
sudo apt update
Step 9: Install pgAdmin4 Web
Now install pgAdmin4 Web:
sudo apt install pgadmin4-web -y
The web version allows you to access pgAdmin from a browser.
Step 10: Setup pgAdmin Web Account
Run the pgAdmin web setup script:
sudo /usr/pgadmin4/bin/setup-web.sh
It will ask for an email and password.
Example:
Email: admin@example.com
Password: StrongPgAdminPasswordHere
This email and password are used only to login to the pgAdmin web panel. This is not your PostgreSQL database password.
pgAdmin Password vs PostgreSQL DB Password
There are two different passwords:
Password Type Purpose
pgAdmin login password Used to login to pgAdmin web UI
PostgreSQL DB password Used to connect to PostgreSQL database user
Do not confuse these two.
Step 11: Configure pgAdmin with System Apache
If pgAdmin is running with system Apache and your main website is running on XAMPP Apache, you may need to run system Apache on a different local port.
Open Apache ports config:
sudo nano /etc/apache2/ports.conf
Add or update:
Listen 127.0.0.1:8081
If Apache is listening on port 80 and your XAMPP Apache is already using it, avoid conflict by keeping system Apache only on 127.0.0.1:8081.
Now edit default Apache site:
sudo nano /etc/apache2/sites-enabled/000-default.conf
Change:
To:
Restart Apache:
sudo systemctl restart apache2
Check status:
sudo systemctl status apache2
Step 12: Expose pgAdmin Through XAMPP Reverse Proxy
If your main website uses XAMPP Apache and you want to open pgAdmin using a URL like:
https://www.example.com/pgadmin4/
Then you can reverse proxy pgAdmin from XAMPP to system Apache.
Open XAMPP SSL config:
sudo nano /opt/lampp/etc/extra/httpd-ssl.conf
Inside your website VirtualHost, add:
ProxyPreserveHost On
ProxyPass /pgadmin4/ http://127.0.0.1:8081/pgadmin4/
ProxyPassReverse /pgadmin4/ http://127.0.0.1:8081/pgadmin4/
RedirectMatch ^/pgadmin4$ /pgadmin4/
Restart XAMPP Apache:
sudo /opt/lampp/lampp restartapache
Now open:
https://www.example.com/pgadmin4/
You should see the pgAdmin login page.
Step 13: Register PostgreSQL Server in pgAdmin
After logging in to pgAdmin, you need to register your PostgreSQL server.
In pgAdmin:
Servers → Register → Server
General Tab
Name: Holiday PostgreSQL
Connection Tab
Use:
Host name/address: 127.0.0.1
Port: 5432
Maintenance database: postgres
Username: holiday_user
Password: StrongAppPasswordHere
If you enable Save password, pgAdmin will store the DB password and will not ask again next time.
If you disable Save password, pgAdmin will ask for the DB password whenever required.
Step 14: Verify PostgreSQL Users and Roles
To check existing PostgreSQL users, login as admin:
sudo -u postgres psql
Run:
\du
This will show users and roles.
Example output:
| Role name | Attributes |
|---|---|
| postgres | Superuser, Create role, Create DB |
| holiday_user | |
| root | Superuser, Create role, Create DB |
If you want to reset a user password:
ALTER USER holiday_user WITH PASSWORD 'NewStrongAppPasswordHere';
Exit:
\q
Step 15: Verify Database Exists and Owner is Correct
Login:
sudo -u postgres psql
List databases:
\l
Or from Linux terminal:
sudo -u postgres psql -c "\l"
If your database exists but has the wrong owner, change owner:
ALTER DATABASE holiday_db OWNER TO holiday_user;
Grant privileges:
GRANT ALL PRIVILEGES ON DATABASE holiday_db TO holiday_user;
Exit:
\q
Step 16: Test PostgreSQL Connection from Linux Terminal
To test the application database user and password:
psql -h 127.0.0.1 -p 5432 -U holiday_user -d holiday_db -W
Enter password.
If successful, you will see:
holiday_db=>
Check current user and database:
SELECT current_database(), current_user;
Check PostgreSQL version:
SHOW server_version;
Exit:
\q
One-Line Test
You can also test using PGPASSWORD:
PGPASSWORD='StrongAppPasswordHere' psql -h 127.0.0.1 -p 5432 -U holiday_user -d holiday_db -c "SELECT current_database(), current_user, inet_server_port(), version();"
If password is wrong, you will see:
FATAL: password authentication failed for user "holiday_user"
If database name is wrong, you will see:
FATAL: database "holiday_db" does not exist
Step 17: Configure Laravel .env for PostgreSQL
In Laravel, configure PostgreSQL in the .env file:
DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=holiday_db
DB_USERNAME=holiday_user
DB_PASSWORD="StrongAppPasswordHere"
If you have multiple microservices, each service may use a separate database:
DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=hl_user
DB_USERNAME=holiday_user
DB_PASSWORD="StrongAppPasswordHere"
Another service:
DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=hl_trip
DB_USERNAME=holiday_user
DB_PASSWORD="StrongAppPasswordHere"
After updating .env, clear Laravel cache:
php artisan config:clear
php artisan cache:clear
Check migration status:
php artisan migrate:status
Step 18: Give Database Create Permission to User
If you want holiday_user to create databases from pgAdmin GUI, give CREATEDB permission.
Login:
sudo -u postgres psql
Run:
ALTER ROLE holiday_user CREATEDB;
Exit:
\q
Now reconnect pgAdmin:
Holiday PostgreSQL → Right click → Disconnect Server
Holiday PostgreSQL → Right click → Connect Server
After reconnecting, right-click:
Databases → Create → Database
Now the user should be able to create a database from GUI.
Step 19: Create Database from pgAdmin GUI
To create a database from pgAdmin:
Login to pgAdmin.
Expand your PostgreSQL server.
Right-click Databases.
Click Create.
Click Database.
Enter database name, for example:
holiday_db
Select owner:
holiday_user
Click Save.
After creation, you should see the new database under Databases.
Step 20: Upgrade PostgreSQL 14 to PostgreSQL 16
A normal apt upgrade does not upgrade PostgreSQL major versions automatically. PostgreSQL 14 to 16 is a major upgrade.
First check current clusters:
pg_lsclusters
Example output:
Ver Cluster Port Status Owner Data directory
14 main 5432 online postgres /var/lib/postgresql/14/main
Install PostgreSQL official repository support:
sudo apt update
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt update
Install PostgreSQL 16:
sudo apt install -y postgresql-16 postgresql-client-16 postgresql-contrib-16
Check client version:
psql --version
However, remember this checks the client version only.
Now upgrade the old PostgreSQL 14 cluster to 16:
sudo pg_upgradecluster -v 16 14 main
Check clusters again:
pg_lsclusters
Expected output:
Ver Cluster Port Status Owner Data directory
14 main 5433 down postgres /var/lib/postgresql/14/main
16 main 5432 online postgres /var/lib/postgresql/16/main
Now PostgreSQL 16 should be running on port 5432.
Step 21: Verify PostgreSQL 16 is Running on Port 5432
To verify from Linux:
sudo -u postgres psql -p 5432 -c "SHOW server_version;"
Expected output:
16.x
To check full version:
sudo -u postgres psql -p 5432 -c "SELECT version();"
To check cluster status:
pg_lsclusters
You should see:
16 main 5432 online
Check from pgAdmin GUI
Open pgAdmin Query Tool and run:
SHOW server_version;
If output is:
16.14
Then pgAdmin is connected to PostgreSQL 16.
Why Linux May Show 16 But pgAdmin Shows 14
If psql --version shows 16 but pgAdmin shows 14, it means your client is 16 but server is still 14.
Always check:
pg_lsclusters
And:
sudo -u postgres psql -p 5432 -c "SHOW server_version;"
Step 22: Fix “Database Does Not Exist” Error
If you run:
psql -h 127.0.0.1 -p 5432 -U holiday_user -d holiday_db -W
And get:
FATAL: database "holiday_db" does not exist
It means the password may be correct, but the database name is wrong or missing.
List databases:
sudo -u postgres psql -p 5432 -c "\l"
If your actual databases are:
hl_user
hl_trip
hl_profile
holidaylandmark
Then use the correct database name in .env:
DB_DATABASE=hl_user
Test again:
psql -h 127.0.0.1 -p 5432 -U holiday_user -d hl_user -W
If you really need holiday_db, create it:
sudo -u postgres createdb -O holiday_user holiday_db
Then test:
psql -h 127.0.0.1 -p 5432 -U holiday_user -d holiday_db -W
Step 23: Secure pgAdmin Saved Password
If pgAdmin opens your database without asking for password, it usually means pgAdmin has saved your DB password.
This does not mean PostgreSQL is unsecured. It means pgAdmin is remembering your password.
To remove saved password:
Login to pgAdmin.
Right-click your server, for example Holiday PostgreSQL.
Click Clear Saved Password.
Right-click server again.
Click Properties.
Go to Connection.
Turn off Save password?.
If Save password? is not clickable, delete and recreate the pgAdmin server connection:
Right-click server.
Click Delete/Drop Server.
Register the server again.
In connection tab, enter:
Host name/address: 127.0.0.1
Port: 5432
Maintenance database: postgres
Username: holiday_user
Password: StrongAppPasswordHere
Save password?: OFF
Now pgAdmin will ask for the DB password again when connecting.
Step 24: Secure PostgreSQL Authentication
PostgreSQL authentication is controlled by the pg_hba.conf file.
Find the file:
sudo -u postgres psql -p 5432 -c "SHOW hba_file;"
Open it:
sudo nano /etc/postgresql/16/main/pg_hba.conf
Avoid using trust authentication in production:
host all all 127.0.0.1/32 trust
Use password-based authentication instead:
host all all 127.0.0.1/32 scram-sha-256
host all all ::1/128 scram-sha-256
Reload PostgreSQL:
sudo pg_ctlcluster 16 main reload
Or:
sudo systemctl reload postgresql
Also check PostgreSQL listen address:
sudo grep -n "listen_addresses" /etc/postgresql/16/main/postgresql.conf
For local-only access:
listen_addresses = 'localhost'
Restart PostgreSQL if you change listen_addresses:
sudo systemctl restart postgresql
Common PostgreSQL Commands Cheat Sheet
Login as PostgreSQL admin
sudo -u postgres psql
List databases
\l
Or:
sudo -u postgres psql -c "\l"
List users and roles
\du
Reset user password
ALTER USER holiday_user WITH PASSWORD 'NewStrongAppPasswordHere';
Check PostgreSQL server version
SHOW server_version;
Check PostgreSQL version from Linux
sudo -u postgres psql -p 5432 -c "SHOW server_version;"
Check PostgreSQL clusters
pg_lsclusters
Test DB login
psql -h 127.0.0.1 -p 5432 -U holiday_user -d holiday_db -W
Exit PostgreSQL shell
\q
Recommended Production Security Checklist
Before using PostgreSQL and pgAdmin in production, follow this checklist:
Do not use PostgreSQL superuser for application.
Create a separate DB user for each application or microservice.
Use a strong password for every database user.
Do not commit .env files to Git.
Do not publish real credentials in blogs or screenshots.
Keep pgAdmin password different from PostgreSQL DB password.
Do not keep pgAdmin publicly accessible without protection.
Use scram-sha-256 authentication.
Avoid trust authentication.
Keep PostgreSQL listening only on required interfaces.
Take full backup before PostgreSQL major version upgrade.
Verify application after upgrade.
Keep old PostgreSQL cluster until testing is complete.
Remove saved pgAdmin password if security is required.
Final Example Laravel PostgreSQL .env
Use actual database name according to your service.
DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=hl_user
DB_USERNAME=holiday_user
DB_PASSWORD="StrongAppPasswordHere"
For another service:
DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=hl_trip
DB_USERNAME=holiday_user
DB_PASSWORD="StrongAppPasswordHere"
After changing .env:
php artisan config:clear
php artisan cache:clear
php artisan migrate:status
Conclusion
In this guide, we installed PostgreSQL and pgAdmin4 Web on Ubuntu, created PostgreSQL users and databases, connected PostgreSQL with pgAdmin, configured Laravel .env, fixed common database connection errors, secured saved passwords, and upgraded PostgreSQL 14 to PostgreSQL 16.
The most important points are:
PostgreSQL default admin user is postgres, not root.
Application should use a separate database user.
pgAdmin login password and PostgreSQL database password are different.
psql --version shows client version, not always server version.
Use SHOW server_version; to verify the actual database server version.
If pgAdmin opens DB without password, it may have saved the DB password.
Always secure PostgreSQL with password authentication like scram-sha-256.
Never publish production credentials.
By following these steps, you can safely install, manage, secure, and upgrade PostgreSQL with pgAdmin on an Ubuntu server.
Top comments (0)