PostgreSQL VPS - Getting Started Guide

Software Included

Component	Version
Ubuntu	24.04 LTS
PostgreSQL	16 (PGDG APT)
Apache	2.4
pgAdmin4	9.x (web mode)
Certbot	latest
Fail2Ban	latest

Step 1 - Your Credentials

Log in to your client panel and open your VPS service details to find the IP address and root password (for SSH).

pgAdmin4 login:

• Email: the email you entered during the order
• Password: the Admin Password you entered during the order

PostgreSQL superuser:

• Username: postgres
• Password: the Admin Password you entered during the order

Step 2 - Access pgAdmin4

pgAdmin4 is a web-based management tool for PostgreSQL. It is available after your VPS is created:

http://your.server.ip/pgadmin4

Log in with the email and Admin Password from Step 1. Then add your PostgreSQL server connection:

1. Right-click Servers in the left panel, select Register > Server
2. General tab: enter a name (e.g. "Local")
3. Connection tab: Host = 127.0.0.1, Port = 5432, Username = postgres, Password = your Admin Password
4. Click Save

You can now browse databases, run SQL queries, import/export data and manage users through the web interface.

Step 3 - Connect to PostgreSQL Remotely

PostgreSQL is configured to accept remote connections. You can connect from any application or tool:

psql -h your.server.ip -U postgres

Connection details for database GUI tools (pgAdmin Desktop, DBeaver, DataGrip, etc.). Note: Port 5432 is the PostgreSQL protocol, not HTTP - it cannot be opened in a browser. Use a database client or the command above.

Parameter	Value
Host	your.server.ip
Port	5432
Username	postgres
Password	Admin Password from order

Note: PostgreSQL accepts connections from any IP address. Authentication uses scram-sha-256. Create databases and users via pgAdmin4 or the command line.

Step 4 - Enable SSL for pgAdmin4 (Optional)

If you have a domain pointing to this server, you can enable HTTPS for pgAdmin4 with a free Let's Encrypt certificate. Connect via SSH:

ssh root@your.server.ip

/opt/setup/get-ssl.sh db.yourdomain.com

The script will verify DNS, obtain a certificate and configure Apache for HTTPS automatically. After completion, pgAdmin4 is available at https://db.yourdomain.com/pgadmin4.

Note: Make sure your domain's A record points to the server IP before running the script. SSL is optional - pgAdmin4 works fine over HTTP with IP address access.

What's Pre-Configured

• Remote access: PostgreSQL accepts connections from any IP on port 5432 (scram-sha-256 authentication)
• shared_buffers: Automatically tuned to 25% of available RAM on first boot
• effective_cache_size: Set to 75% of RAM for optimal query planning
• Security: Fail2Ban protects SSH from brute-force attacks
• Encoding: Databases are created with UTF-8 encoding (utf8, en_US.UTF-8)

Creating Additional Databases

You can create additional databases via pgAdmin4 or SSH:

sudo -u postgres psql

CREATE USER newuser WITH PASSWORD 'strong_password';
CREATE DATABASE newdb OWNER newuser ENCODING 'UTF8';
GRANT ALL PRIVILEGES ON DATABASE newdb TO newuser;

Import and Export

Via pgAdmin4: Right-click a database, select Backup or Restore. Supports custom, tar, plain and directory formats.

Via command line:

# Export
pg_dump -U postgres your_database > backup.sql

# Export (custom format, compressed)
pg_dump -U postgres -Fc your_database > backup.dump

# Import (SQL)
psql -U postgres your_database < backup.sql

# Import (custom format)
pg_restore -U postgres -d your_database backup.dump

For large databases, use pg_dump -Fc (custom format) for faster and smaller backups.

Fail2Ban - Brute-Force Protection

Your server comes with Fail2Ban pre-configured to protect SSH from brute-force attacks.

Rule	Max Attempts	Ban Duration
SSH	5 failed logins	10 minutes

Useful commands:

# Check banned IPs
fail2ban-client status sshd

# Unban an IP
fail2ban-client set sshd unbanip 1.2.3.4

If you accidentally lock yourself out, connect via VNC console in your client panel and unban your IP.

PostgreSQL Performance Tuning

PostgreSQL is automatically tuned on first boot based on available RAM. To check the current settings:

sudo -u postgres psql -c "SHOW shared_buffers; SHOW effective_cache_size; SHOW work_mem;"

To adjust manually, edit /etc/postgresql/16/main/conf.d/99-tuning.conf and restart PostgreSQL:

systemctl restart postgresql

Server Credentials

PostgreSQL and pgAdmin4 credentials are stored in /root/.pg_credentials.

Updates

PostgreSQL and the OS don't update automatically. To update manually:

apt update && apt upgrade -y

• PostgreSQL 16 Documentation: postgresql.org/docs/16
• pgAdmin4 Documentation: pgadmin.org/docs