--- title: "How to install PostgreSQL on an Ubuntu VPS securely | StreetHosting" description: "Install PostgreSQL on an Ubuntu VPS, create a user and database, allow remote access by IP only, schedule pg_dump backups and tune memory and connections." url: "https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps" type: "page" language: "en-US" --- VPS · 10 min · Intermediate Published on Jun 18, 2026 · Updated on Sep 28, 2026 # PostgreSQL on an Ubuntu VPS: secure access, backups and tuning PostgreSQL is the most complete open source relational database and the default for many modern applications. Learn how to install it on an Ubuntu VPS, create a user and database for your application, understand authentication, allow remote access without exposing the port, schedule backups and tune memory. By [Equipe StreetHosting](https://streethosting.com.br/en/autores#equipe-streethosting) · StreetHosting infrastructure and support team [Linux administration](https://streethosting.com.br/en/guides/topics/linux) [Performance and optimization](https://streethosting.com.br/en/guides/topics/performance) [Security and hardening](https://streethosting.com.br/en/guides/topics/security) [Databases](https://streethosting.com.br/en/guides/topics/databases) Summarize with: [](https://chat.openai.com/?q=Summarize%20the%20key%20points%20of%20this%20StreetHosting%20guide%3A%20https%3A%2F%2Fstreethosting.com.br%2Fen%2Fguides%2Fvps%2Finstall-postgresql-ubuntu-vps.%20Highlight%20the%20step-by-step%20instructions%2C%20the%20prerequisites%20and%20the%20most%20common%20mistakes. "ChatGPT") [](https://claude.ai/new?q=Summarize%20the%20key%20points%20of%20this%20StreetHosting%20guide%3A%20https%3A%2F%2Fstreethosting.com.br%2Fen%2Fguides%2Fvps%2Finstall-postgresql-ubuntu-vps.%20Highlight%20the%20step-by-step%20instructions%2C%20the%20prerequisites%20and%20the%20most%20common%20mistakes. "Claude") [](https://www.google.com/search?udm=50&aep=11&q=Summarize%20the%20key%20points%20of%20this%20StreetHosting%20guide%3A%20https%3A%2F%2Fstreethosting.com.br%2Fen%2Fguides%2Fvps%2Finstall-postgresql-ubuntu-vps.%20Highlight%20the%20step-by-step%20instructions%2C%20the%20prerequisites%20and%20the%20most%20common%20mistakes. "Google AI Mode") [](https://x.com/i/grok?text=Summarize%20the%20key%20points%20of%20this%20StreetHosting%20guide%3A%20https%3A%2F%2Fstreethosting.com.br%2Fen%2Fguides%2Fvps%2Finstall-postgresql-ubuntu-vps.%20Highlight%20the%20step-by-step%20instructions%2C%20the%20prerequisites%20and%20the%20most%20common%20mistakes. "Grok") [](https://www.perplexity.ai/search/new?q=Summarize%20the%20key%20points%20of%20this%20StreetHosting%20guide%3A%20https%3A%2F%2Fstreethosting.com.br%2Fen%2Fguides%2Fvps%2Finstall-postgresql-ubuntu-vps.%20Highlight%20the%20step-by-step%20instructions%2C%20the%20prerequisites%20and%20the%20most%20common%20mistakes. "Perplexity") Share: [](https://x.com/intent/tweet?text=How%20to%20install%20PostgreSQL%20on%20an%20Ubuntu%20VPS%20securely&url=https%3A%2F%2Fstreethosting.com.br%2Fen%2Fguides%2Fvps%2Finstall-postgresql-ubuntu-vps "Share on X") [](https://www.facebook.com/sharer/sharer.php?u=https%3A%2F%2Fstreethosting.com.br%2Fen%2Fguides%2Fvps%2Finstall-postgresql-ubuntu-vps "Share on Facebook") [](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fstreethosting.com.br%2Fen%2Fguides%2Fvps%2Finstall-postgresql-ubuntu-vps "Share on LinkedIn") [](https://wa.me/?text=How%20to%20install%20PostgreSQL%20on%20an%20Ubuntu%20VPS%20securely%20https%3A%2F%2Fstreethosting.com.br%2Fen%2Fguides%2Fvps%2Finstall-postgresql-ubuntu-vps "Share on WhatsApp") For agents: Copy as Markdown [.md](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps.md) In this guide 8 sections * [Install PostgreSQL](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#instalar) * [Create a user and database](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#criar-usuario) * [How authentication works](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#autenticacao) * [Connect your application](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#conectar-app) * [Secure remote access](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#seguranca) * [Basic tuning](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#otimizacao) * [Scheduled backups with pg\_dump](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#backup) * [Which VPS to use for PostgreSQL](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#onde-rodar) Quick answer To **install PostgreSQL on an Ubuntu VPS**, run `sudo apt install postgresql`, create a dedicated user and database for your application and keep the service listening only on localhost. For remote access, allow a specific IP in _pg\_hba.conf_ and in the firewall, never the whole of port 5432. Schedule a daily `pg_dump` and tune _shared\_buffers_ and connections to the size of the VPS. ## Install PostgreSQL[](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#instalar) 1. Update the package lists: `sudo apt update` 2. Install PostgreSQL: `sudo apt install -y postgresql postgresql-contrib` 3. Confirm the service is running: `sudo systemctl status postgresql` 4. Check the version and the cluster that was created: `pg_lsclusters` Ubuntu 24.04 installs PostgreSQL 16, and 22.04 installs 14. The package already creates a cluster called _main_, starts the service and sets it to come up on boot. The configuration files live in `/etc/postgresql/16/main/` and the data in `/var/lib/postgresql/16/main/`. In the commands throughout this guide, replace 16 with the version that `pg_lsclusters` shows. The version Ubuntu ships receives security fixes for the whole support period of the system. If you need a newer major version, the project's official repository, PGDG, has a script that sets it up: `sudo apt install -y postgresql-common && sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh`. Then install the version you want, such as `postgresql-18`. ## Create a user and database[](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#criar-usuario) PostgreSQL creates a superuser called _postgres_, tied to the system user of the same name. Open the shell as that user: `sudo -u postgres psql` Inside psql, create a dedicated user for the application and a database owned by it: `CREATE USER meu_usuario; \password meu_usuario CREATE DATABASE meu_banco OWNER meu_usuario; \q` `\password` prompts for the password twice and stores only the hashed version, without leaving the plain text in the psql history. The important detail is `OWNER`: since PostgreSQL 15, regular users can no longer create tables in the _public_ schema of a database they do not own. A `GRANT ALL PRIVILEGES ON DATABASE` does not fix it, because it grants the right to connect and create schemas, but not to create tables in _public_. Creating the database with the right owner from the start avoids the _permission denied for schema public_error on the application's first migration. Never use the postgres user in your application. Create one user per application, with access only to the database it uses. If the code leaks, the damage stays confined to that database. ## How authentication works[](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#autenticacao) The file `/etc/postgresql/16/main/pg_hba.conf` decides who connects, from where and with which method. It is read from top to bottom, and the first line that matches the connection wins. On an Ubuntu install, these are the default rules: | Connection type | Default method | What it means | | --------------------------- | -------------- | ------------------------------------------------------------------- | | Local, over the Unix socket | peer | The system user name must match the database user name, no password | | TCP on 127.0.0.1 or ::1 | SCRAM | Any database user, with the password verified by SCRAM | | TCP from another IP | No rule | Refused until you create a rule | In practice, this means an application on the same VPS that connects to _127.0.0.1_ with a user and password already works without editing anything. It is also why `psql -U meu_usuario meu_banco` fails with a peer authentication error: without `-h 127.0.0.1`, psql uses the socket and the peer rule. If you also want password authentication over the socket, add the line below before the `local all all peer` rule and reload with `sudo systemctl reload postgresql`: `local meu_banco meu_usuario scram-sha-256` Older tutorials use the _md5_ method. Prefer `scram-sha-256`, the default since PostgreSQL 14. With md5, the hash stored on the server is enough to authenticate, so anyone who obtains it gets in without knowing the password; SCRAM removes that shortcut. ## Connect your application[](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#conectar-app) The standard PostgreSQL connection string, which most drivers and ORMs accept in a variable such as _DATABASE\_URL_: `postgresql://meu_usuario:SENHA@127.0.0.1:5432/meu_banco` * **Node.js:** the `pg` library or ORMs such as Prisma and Drizzle. See the guide on [hosting a Node.js API on a VPS](https://streethosting.com.br/en/guides/vps/host-nodejs-on-vps). * **Python:** `psycopg` or SQLAlchemy, common in FastAPI and Django projects. * **PHP:** PDO with the pgsql driver; in Laravel, `DB_CONNECTION=pgsql` in _.env_ is all it takes. Passwords containing _@_, _:_ or _/_ have to be URL-encoded. To sidestep the problem, generate passwords with letters and numbers only, for example with `openssl rand -hex 24`. ## Secure remote access[](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#seguranca) If the application runs on the same VPS, skip this section: the database listening only on localhost is the most secure setup. To accept connections from another server, three files change. In `postgresql.conf`, _listen\_addresses_takes the addresses of the VPS itself on which PostgreSQL will listen, not the client's IP, which is a common mistake: `# /etc/postgresql/16/main/postgresql.conf listen_addresses = 'localhost,10.8.0.1' # /etc/postgresql/16/main/pg_hba.conf hostssl meu_banco meu_usuario IP_DO_SERVIDOR_APP/32 scram-sha-256 # firewall sudo ufw allow from IP_DO_SERVIDOR_APP to any port 5432 proto tcp sudo systemctl restart postgresql` In the example, 10.8.0.1 is the address of the VPS inside a [WireGuard](https://streethosting.com.br/en/guides/vps/wireguard-vpn-vps) tunnel; if you are not using a VPN, put the public IP of the VPS there. The `hostssl` rule only accepts the connection over TLS, and the PostgreSQL that Ubuntu ships already comes with TLS enabled, using a self-signed certificate. On the client, add `?sslmode=require` to the URL. Changing _listen\_addresses_ requires a restart; changes only to _pg\_hba.conf_ need just a reload. The firewall rules follow the [UFW on an Ubuntu VPS guide](https://streethosting.com.br/en/guides/vps/ufw-firewall-ubuntu-vps). To manage the database from DBeaver or pgAdmin on your computer, do not open the port: create a tunnel with `ssh -N -L 5433:127.0.0.1:5432 usuario@IP_DA_VPS` and connect to _localhost_, port 5433. Never combine `listen_addresses = '*'` with a _0.0.0.0/0_ rule in pg\_hba.conf and the port open in the firewall. Bots try passwords on port 5432 around the clock, and a compromised PostgreSQL usually ends up as a cryptocurrency miner. ## Basic tuning[](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#otimizacao) The default PostgreSQL configuration is conservative on purpose, so it runs even on tiny machines. On a VPS with a few GB of RAM, four memory parameters and the connection limit make the biggest difference: * **shared\_buffers:** the cache PostgreSQL manages itself. The starting point is 25% of RAM; more than that rarely helps, because the operating system file cache also holds the data. Changing it requires a restart. * **effective\_cache\_size:** does not reserve memory. It is an estimate, used by the query planner, of how much cache exists when you add the PostgreSQL cache and the system cache together. Somewhere between 50% and 75% of RAM. * **work\_mem:** memory for each sort or grouping operation, per operation and per connection. A complex query uses several at the same time, so 100 connections at 64 MB can exceed 6 GB during a spike. Raise it carefully. * **maintenance\_work\_mem:** memory for VACUUM and index creation. A larger value speeds those tasks up without affecting regular queries. * **max\_connections:** the default is 100. Each connection is a process with its own memory, so raising it to 500 or 1000 is almost always the wrong fix. The answer to many connections is a pooler. | RAM of a VPS dedicated to the database | shared\_buffers | effective\_cache\_size | work\_mem | maintenance\_work\_mem | | -------------------------------------- | --------------- | ---------------------- | --------- | ---------------------- | | 4 GB | 1GB | 3GB | 8MB | 256MB | | 8 GB | 2GB | 6GB | 16MB | 512MB | | 16 GB | 4GB | 12GB | 32MB | 1GB | | 32 GB | 8GB | 24GB | 64MB | 2GB | If the application runs on the same VPS, use the row for half of your RAM. Instead of editing the main file, create your own file in _conf.d_, which Ubuntu includes by default. Confirm with `grep include_dir /etc/postgresql/16/main/postgresql.conf`; if the line is missing, edit the same parameters in _postgresql.conf_ itself. `# /etc/postgresql/16/main/conf.d/ajustes.conf (8 GB VPS dedicated to the database) shared_buffers = 2GB effective_cache_size = 6GB work_mem = 16MB maintenance_work_mem = 512MB max_connections = 100 random_page_cost = 1.1 sudo systemctl restart postgresql sudo -u postgres psql -c "SHOW shared_buffers;"` Setting _random\_page\_cost_ to 1.1 tells the planner that a random read on NVMe costs almost the same as a sequential one, which leads it to use indexes more often. To find out whether _work\_mem_ is too low, run a slow query with `EXPLAIN (ANALYZE, BUFFERS)`: an _external merge Disk_ entry shows a sort that did not fit in memory. ### When to use PgBouncer[](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#pgbouncer) PgBouncer sits between the application and the database and reuses a few real connections to serve many client connections. It makes sense when you see the _sorry, too many clients already_ error, when the application opens and closes connections all the time, as PHP and serverless functions do, or when several instances of the application add up to more connections than the database can handle. `sudo apt install -y pgbouncer # copy the password hash into the PgBouncer user file sudo -u postgres psql -Atc "SELECT rolpassword FROM pg_authid WHERE rolname = 'meu_usuario'" # /etc/pgbouncer/userlist.txt # paste the full value returned, which starts with SCRAM-SHA-256$4096: "meu_usuario" "VALOR_RETORNADO_PELA_CONSULTA" # /etc/pgbouncer/pgbouncer.ini (adjust or add these lines) [databases] meu_banco = host=127.0.0.1 port=5432 dbname=meu_banco [pgbouncer] listen_addr = 127.0.0.1 listen_port = 6432 auth_type = scram-sha-256 auth_file = /etc/pgbouncer/userlist.txt pool_mode = transaction max_client_conn = 500 default_pool_size = 20 sudo systemctl restart pgbouncer` The application then uses port 6432 in its connection string. The _transaction_ mode saves the most connections, but it breaks features that depend on the session, such as _SET_ outside a transaction, advisory locks, _LISTEN_ and temporary tables. Some ORMs have their own option for this scenario, such as the `pgbouncer=true` parameter in Prisma. Schema migrations should keep going straight to port 5432. ## Scheduled backups with pg\_dump[](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#backup) `pg_dump` produces a consistent copy of the database without stopping the service. The custom format (`-Fc`) comes out compressed and lets you restore specific tables with `pg_restore`. Create the script `/usr/local/bin/backup-postgres.sh`: `#!/usr/bin/env bash set -euo pipefail DESTINO="/var/backups/postgres" DATA="$(date +%F_%H%M)" mkdir -p "$DESTINO" chmod 700 "$DESTINO" sudo -u postgres pg_dump -Fc meu_banco > "$DESTINO/meu_banco_$DATA.dump" sudo -u postgres pg_dumpall --globals-only > "$DESTINO/globais_$DATA.sql" find "$DESTINO" -type f -mtime +7 -delete` `pg_dumpall --globals-only` saves users and permissions, which are not included in the dump of a single database. The _find_ deletes copies older than seven days. Make the script executable and schedule it in the root crontab: `sudo chmod 700 /usr/local/bin/backup-postgres.sh sudo crontab -e # every day at 3:15 AM 15 3 * * * /usr/local/bin/backup-postgres.sh >> /var/log/backup-postgres.log 2>&1` The schedule syntax is explained in the guide on [scheduled tasks with cron](https://streethosting.com.br/en/guides/vps/schedule-tasks-vps-cron). A backup that lives only on the VPS itself disappears together with it, so ship the folder to an external destination using the method from the guide on [automated backups with restic and cron](https://streethosting.com.br/en/guides/vps/vps-backup-restic-cron). And test the restore, because a dump that was never restored proves nothing: `sudo -u postgres createdb teste_restauracao sudo -u postgres pg_restore -d teste_restauracao /var/backups/postgres/meu_banco_2026-09-28_0315.dump sudo -u postgres psql -d teste_restauracao -c "\dt" sudo -u postgres dropdb teste_restauracao` To restore over the production database, use `pg_restore --clean --if-exists -d meu_banco arquivo.dump`. A daily dump can lose up to a day of data. If that is too much for your business, the next step is to archive the WAL continuously with a tool such as pgBackRest, which lets you roll the database back to any point in time. ## Which VPS to use for PostgreSQL[](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#onde-rodar) PostgreSQL performance scales with the RAM available for cache and with the number of cores available to serve concurrent connections. That is why the [Xeon VPS](https://streethosting.com.br/en/vps/xeon), which delivers more memory and vCPU per R$ spent, is the most common starting point: 8 GB with 6 vCPU and 80 GB of NVMe costs R$ 77.00, 16 GB with 9 vCPU and 160 GB costs R$ 145.00 and 32 GB with 15 vCPU and 320 GB costs R$ 281.00. When the bottleneck is the time heavy queries take, the Ryzen 9 9950X VPS with DDR5 and up to 5.7 GHz speeds up each one, starting at R$ 118.00 with 4 vCPU and 8 GB. Every [StreetHosting VPS](https://streethosting.com.br/en/vps) plan uses NVMe, is located in São Paulo and comes with Anti-DDoS and root access. Upgrading through the control panel charges only the prorated difference and restarts the VM; after it, adjust _shared\_buffers_ and the other parameters to the new RAM, because PostgreSQL does not do that on its own. The full sizing math, with a table by database size, is in [how to choose a VPS for a database](https://streethosting.com.br/en/guides/vps/choose-vps-for-database). In this guide * [Install PostgreSQL](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#instalar) * [Create a user and database](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#criar-usuario) * [How authentication works](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#autenticacao) * [Connect your application](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#conectar-app) * [Secure remote access](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#seguranca) * [Basic tuning](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#otimizacao) * [Scheduled backups with pg\_dump](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#backup) * [Which VPS to use for PostgreSQL](https://streethosting.com.br/en/guides/vps/install-postgresql-ubuntu-vps#onde-rodar) ## Frequently asked questions PostgreSQL or MySQL for my application? PostgreSQL is more complete for complex data, with native JSON, advanced types and sophisticated queries, and it is the default for frameworks such as Django and Rails and for much of the Node.js ecosystem. MySQL and MariaDB are simpler and remain the standard for platforms such as WordPress. How do I connect to PostgreSQL remotely? By default PostgreSQL only listens locally. For remote access, add the address of the VPS itself to listen\_addresses in postgresql.conf, add a rule in pg\_hba.conf for the client's IP with the SCRAM method and open port 5432 in the firewall only for that IP. To manage it from your computer, an SSH tunnel avoids opening the port. Which port does PostgreSQL use? PostgreSQL's default port is 5432. PgBouncer, when used, usually listens on 6432. If you need remote access, open the port in the firewall only for the IPs that really need to connect. How do I back up PostgreSQL? Use pg\_dump in custom format, with the -Fc option, which produces a compressed file you can restore with pg\_restore. Schedule it with cron every day, delete old copies automatically, send the files off the VPS and test the restore on a separate database. How much shared\_buffers should I use in PostgreSQL? On a VPS dedicated to the database, the starting point is 25% of RAM, with effective\_cache\_size between 50% and 75%. If the application runs on the same VPS, scale it down proportionally. After changing shared\_buffers you need to restart the service. Next step See Xeon VPS Xeon VPS for steady workloads, automation and long-running projects. [See Xeon VPS](https://streethosting.com.br/en/vps/xeon) [See VPS plans Root VPS in Brazil with NVMe and Anti-DDoS.](https://streethosting.com.br/en/vps) [See Ryzen VPS Ryzen 9 9950X VPS in São Paulo with root access, NVMe and gamer Anti-DDoS.](https://streethosting.com.br/en/vps/ryzen) ## Related guides [VPS Intermediate How to choose a VPS for a database: RAM, CPU and NVMe A slow database is rarely fixed with more vCPUs: almost always it is short on RAM for the hot data or stuck on disk latency. Learn how to measure what your database actually consumes and pick the plan by the right number. 9 min Read guide](https://streethosting.com.br/en/guides/vps/choose-vps-for-database) [VPS Beginner UFW on Ubuntu VPS: firewall rules without losing SSH UFW makes the Ubuntu firewall simpler, but one rule in the wrong order locks you out of your VPS. Learn how to enable it without losing SSH, open only what you need, deal with Docker, and get back in through the console if something goes wrong. 10 min Read guide](https://streethosting.com.br/en/guides/vps/ufw-firewall-ubuntu-vps) [VPS Advanced How to automate VPS backups with restic and cron A backup that depends on you remembering does not work. With restic and cron you create encrypted snapshots, send them off the VPS, control retention and test restores without daily effort. 9 min Read guide](https://streethosting.com.br/en/guides/vps/vps-backup-restic-cron) [← Back to the Guide Center](https://streethosting.com.br/en/guides)