PostgreSQL - Getting Started Guide

Step 1 - Verify PostgreSQL Works

Your server's IP address and root password are shown on the Server Details page in your client panel. pgAdmin4 is a web-based management tool for PostgreSQL. Open http://your.server.ip/pgadmin4 in a browser to verify it works.

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

Log in to pgAdmin4, 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.

Database servers don't need public internet access. For secure access, place the server on a private network behind a VyOS router and connect over a VPN to reach PostgreSQL and pgAdmin4 on the server's private IP.

Step 2 - 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.):

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

Note: Port 5432 is the PostgreSQL protocol, not HTTP - it cannot be opened in a browser. Use a database client or the command above.

Step 3 - 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 and run:

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.

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.

Service Management

PostgreSQL and Apache run via systemd. Common commands:

# Check service status
systemctl status postgresql
systemctl status apache2

# Restart PostgreSQL
systemctl restart postgresql

# View PostgreSQL logs
journalctl -u postgresql -n 100

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.

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
• Encoding: Databases are created with UTF-8 encoding (utf8, en_US.UTF-8)

Software Included

Component	Version
Ubuntu	24.04 LTS
PostgreSQL	16 (PGDG APT)
Apache	2.4
pgAdmin4	Latest (web mode)
Certbot	Latest
Fail2Ban	1.0

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