This guide covers how to set up and configure postgresql on a Linux VPS. Whether you're running a production environment or a development setup, these instructions will help you get started quickly and securely.
Prerequisites
- A database client tool for testing connections
- The target database server installed and running
- A VPS running Ubuntu 22.04 or later (2GB+ RAM recommended)
Installation and Initial Setup
Regular maintenance is essential for keeping your postgresql installation running smoothly. Schedule periodic reviews of log files, disk usage, and security updates to prevent issues before they occur.
-- Check current configuration
SHOW VARIABLES LIKE '%pgbouncer%';
SHOW STATUS LIKE '%pgbouncer%';
-- Optimize settings
SET GLOBAL pgbouncer_size = '256M';
The configuration above sets the recommended values for a VPS with 2-4GB of RAM. Adjust the memory-related settings proportionally if your server has different specifications.
- Start with the minimum required resources
- Profile before optimizing - measure first
- Implement caching at every appropriate layer
Configuration Tuning
If you encounter issues during setup, check the system logs first. Most problems can be diagnosed by examining the output of journalctl or the application-specific log files in /var/log/.
# Configuration file: /etc/mysql/mysql.conf.d/mysqld.cnf
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Add these optimizations:
[mysqld]
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
max_connections = 200
# Restart the service
sudo systemctl restart mysql
Make sure to restart the service after applying these changes. Some settings require a full restart rather than a reload to take effect.
- Monitor disk space usage and set up alerts
- Enable automatic security updates for critical patches
- Test your backup restore procedure monthly
Wrapping Up
Following this guide, your postgresql setup should be production-ready. Keep an eye on resource usage as your traffic grows and don't forget to test your backup and recovery procedures periodically.