PostgreSQL

PostgreSQL is a powerful open-source relational database suitable for everything from small applications to large enterprise systems. Piora lets you quickly create and manage PostgreSQL instances.

Creating a PostgreSQL Database

1. Navigate to the “Databases” page in the dashboard
2. Select “PostgreSQL”
3. Choose a version (PostgreSQL 16 recommended)
4. Configure the following parameters:

Parameter	Description	Example
Database name	Service identifier	my-postgres
Username	Admin account	piora_admin
Password	Admin password	(auto-generated or custom)
Default database	Initially created database	myapp

Password Security

We recommend using Piora’s auto-generated random passwords to ensure sufficient password strength.

Connection Information

After creation, you can get the connection string from the dashboard:

# Standard connection string
postgresql://piora_admin:your-password@postgres-container:5432/myapp

# Or use individual parameters
Host: postgres-container
Port: 5432
Database: myapp
Username: piora_admin
Password: your-password

Using in Your Application

Set the connection string as an environment variable for your app:

DATABASE_URL=postgresql://piora_admin:your-password@postgres-container:5432/myapp

Connection examples for common frameworks:

// Node.js (pg)
const { Pool } = require('pg');
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

# Python (psycopg2)
import psycopg2
conn = psycopg2.connect(os.environ['DATABASE_URL'])

Performance Tuning

Memory Settings

Adjust PostgreSQL’s memory allocation based on your VPS memory:

VPS Memory	shared_buffers	effective_cache_size	work_mem
1 GB	256 MB	512 MB	4 MB
2 GB	512 MB	1.5 GB	8 MB
4 GB	1 GB	3 GB	16 MB

You can modify these parameters in the Piora dashboard or override them with a custom configuration file.

Backup and Restore

Automatic Backups

Piora supports automatic scheduled backups for PostgreSQL databases. See the Database Backup section for details.

Manual Backup

You can also run manual backups through the dashboard terminal:

# Export database
pg_dump -U piora_admin -d myapp > backup.sql

# Restore database
psql -U piora_admin -d myapp < backup.sql

Common Issues

Connection Refused

Ensure your application and database are on the same Docker network and that you’re using the container name rather than localhost.

Slow Performance

1. Check if shared_buffers is configured appropriately
2. Use EXPLAIN ANALYZE to analyze slow queries
3. Ensure tables have proper indexes
4. Monitor whether connection count exceeds the limit

Note

PostgreSQL’s default maximum connections is 100. If your application uses connection pooling, make sure the pool size doesn’t exceed this limit.