PostgreSQL VPS Hosting - Dedicated Database Server Offshore

Managed PostgreSQL services like RDS, Supabase, or ElephantSQL restrict superuser access, block extensions that require C libraries (PostGIS, pg_trgm with custom dictionaries, TimescaleDB on lower tiers), and charge premium prices for storage. A self-hosted PostgreSQL VPS gives you full superuser control, the ability to install any extension from the PostgreSQL extension ecosystem, and storage at VPS pricing rather than per-GB database pricing. You configure pg_hba.conf to allow exactly the connections your application needs, set max_connections and shared_buffers for your RAM, and run VACUUM and ANALYZE on your schedule.

Install PostgreSQL 16 from the official repository: ```bash apt install -y postgresql-common /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh apt install -y postgresql-16 postgresql-16-postgis-3 ``` Key postgresql.conf tuning for a 4GB RAM VPS: ```ini shared_buffers = 1GB effective_cache_size = 3GB maintenance_work_mem = 256MB checkpoint_completion_target = 0.9 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 1.1 effective_io_concurrency = 200 max_connections = 100 ``` Use the pgTune calculator (pgtune.leopard.in.ua) for workload-specific recommendations. These settings alone typically double query performance vs. PostgreSQL's conservative defaults.

PostgreSQL logical replication lets you replicate specific tables or databases to another PostgreSQL instance - useful for read replicas, cross-version upgrades, or data warehousing. Enable it in postgresql.conf: ```ini wal_level = logical max_replication_slots = 4 max_wal_senders = 4 ``` Create a publication on the primary: ```sql CREATE PUBLICATION mypub FOR ALL TABLES; ``` Subscribe on the replica: ```sql CREATE SUBSCRIPTION mysub CONNECTION 'host=primary-ip dbname=mydb user=replicator password=secret' PUBLICATION mypub; ``` AnubizHost VPS plans support private networking between instances, keeping replication traffic off the public internet and reducing latency between primary and replica.

Bind PostgreSQL to localhost only unless your application server is on a separate host: ```ini listen_addresses = '127.0.0.1' ``` For cross-host connections, use pg_hba.conf to restrict to specific IPs and require scram-sha-256 authentication. Automate daily backups with pg_dump: ```bash 0 3 * * * postgres pg_dump mydb | gzip > /backups/mydb-$(date +%Y%m%d).sql.gz ``` Use pgBackRest for incremental WAL archiving if you need point-in-time recovery capability. Store backups on a separate VPS or object storage service - never on the same disk as the database, as a disk failure would destroy both simultaneously.