PostgreSQL Setup

This guide will walk you through configuring your existing PostgreSQL database for use with Sync or Swim. It covers both self-hosted PostgreSQL databases and cloud-hosted services like Amazon RDS, Google Cloud SQL, and Azure Database for PostgreSQL.

Overview

Sync or Swim connects to your PostgreSQL database to synchronize data bidirectionally with other services. You’ll need to:

1. Create a dedicated user with appropriate permissions
2. Configure network access to allow Sync or Swim connections
3. Gather connection credentials

Prerequisites

• Existing PostgreSQL database (version 12 or higher recommended)
• Administrative access to the database server or superuser privileges
• Network access to the database server from where Sync or Swim is running
• Connection details (hostname, port, admin credentials)

Self-Hosted PostgreSQL Database

This section assumes you already have a PostgreSQL server running and have administrative access to it.

1. Access Your PostgreSQL Database

Connect to your PostgreSQL database using an administrative user:

# Using postgres superuser (Linux/macOS)
sudo -u postgres psql

# Or connect with your admin credentials
psql -U your_admin_user -h your_database_host -d postgres

# On Windows, use pgAdmin or psql from Start Menu
psql -U postgres

2. Create a Database (Optional)

You can either create a new database for Sync or Swim or use an existing one:

-- Option A: Create a new database
CREATE DATABASE your_database_name;

-- Option B: Use an existing database (skip to step 3)
-- Just note the database name you want to use

3. Create a Dedicated User

-- Create a user for Sync or Swim
CREATE USER sync_user WITH PASSWORD 'your_secure_password';

-- Grant privileges on the database (replace 'your_database_name' with your database)
GRANT ALL PRIVILEGES ON DATABASE your_database_name TO sync_user;

-- Connect to the database
\c your_database_name

-- Grant schema privileges (PostgreSQL 15+)
GRANT ALL ON SCHEMA public TO sync_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO sync_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO sync_user;

-- For future tables (PostgreSQL 10+)
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO sync_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO sync_user;

4. Configure Network Access (If Required)

If Sync or Swim will connect from a different server, you may need to configure PostgreSQL to accept remote connections.

Check if remote access is already configured:

# Test connection from Sync or Swim server
psql -U sync_user -d your_database_name -h your_database_host

If the connection fails, you’ll need to configure PostgreSQL:

Find your PostgreSQL config directory:

# Show config file location
sudo -u postgres psql -c 'SHOW config_file;'

Edit postgresql.conf:

/etc/postgresql/[version]/main/postgresql.conf

# Common locations:
# RHEL/CentOS: /var/lib/pgsql/[version]/data/postgresql.conf
# macOS (Homebrew): /opt/homebrew/var/postgresql@[version]/postgresql.conf

sudo nano /etc/postgresql/16/main/postgresql.conf

Find and uncomment/modify:

listen_addresses = '*'  # Or specify specific IP addresses for better security
port = 5432

Edit pg_hba.conf:

sudo nano /etc/postgresql/16/main/pg_hba.conf

Add a line to allow connections from Sync or Swim server (adjust as needed for security):

# TYPE  DATABASE            USER            ADDRESS                 METHOD
# Replace with your Sync or Swim server IP address
host    your_database_name  sync_user       192.168.1.100/32        scram-sha-256

# Or for a subnet
host    your_database_name  sync_user       192.168.1.0/24          scram-sha-256

# Only use 0.0.0.0/0 for testing, not production!
# host    your_database_name  sync_user       0.0.0.0/0               scram-sha-256

Restart PostgreSQL:

# Ubuntu/Debian
sudo systemctl restart postgresql

# RHEL/CentOS
sudo systemctl restart postgresql-16

# macOS (Homebrew)
brew services restart postgresql@16

5. Verify the Setup

Test the connection from the Sync or Swim server:

# Test connection from Sync or Swim server
psql -U sync_user -d your_database_name -h your_database_host

# If successful, you should see:
# psql (16.x)
# Type "help" for help.
# your_database_name=>

# Test permissions
\dt  # Should list tables (if any exist)
\q   # Quit

If the connection is successful, you’re ready to configure Sync or Swim!

Amazon RDS for PostgreSQL

For existing RDS instances, you’ll need to create a user for Sync or Swim and configure security groups.

1. Configure Security Group

1. Navigate to EC2 → Security Groups in AWS Console
2. Find the security group assigned to your RDS instance
3. Add an inbound rule:
   • Type: PostgreSQL
   • Protocol: TCP
   • Port: 5432
   • Source: Your Sync or Swim server’s IP address or security group

2. Create Application User

Connect to your RDS instance using your master/admin user:

psql -h your-rds-endpoint.region.rds.amazonaws.com \
     -U your_master_user \
     -d postgres

Create a dedicated user for Sync or Swim:

-- Optional: Create database if you don't have one
-- CREATE DATABASE your_database_name;

-- Create application user
CREATE USER sync_user WITH PASSWORD 'your_secure_password';

-- Grant privileges (use your database name)
GRANT ALL PRIVILEGES ON DATABASE your_database_name TO sync_user;

-- Connect to the database
\c your_database_name

GRANT ALL ON SCHEMA public TO sync_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO sync_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO sync_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO sync_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO sync_user;

3. Connection Details

You’ll need:

• Hostname: Found in RDS console (e.g., your-instance.abc123.us-east-1.rds.amazonaws.com)
• Port: 5432 (default)
• Database: Your database name
• Username: sync_user
• Password: The password you set
• SSL: true (RDS requires SSL by default)

Google Cloud SQL for PostgreSQL

For existing Cloud SQL instances, you’ll need to configure authorized networks and create a user.

1. Configure Network Access

1. Navigate to your Cloud SQL instance in the Cloud SQL Console
2. Go to Connections → Networking
3. Under Authorized networks, add your Sync or Swim server’s IP address

2. Create Database and User

Connect to your Cloud SQL instance:

# Using gcloud
gcloud sql connect your-instance-name --user=postgres

# Or using psql with public IP
psql -h your-instance-ip -U postgres -d postgres

Create database and user:

-- Optional: Create database if you don't have one
-- CREATE DATABASE your_database_name;

-- Create user
CREATE USER sync_user WITH PASSWORD 'your_secure_password';

-- Grant privileges (use your database name)
GRANT ALL PRIVILEGES ON DATABASE your_database_name TO sync_user;

-- Connect to the database
\c your_database_name

GRANT ALL ON SCHEMA public TO sync_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO sync_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO sync_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO sync_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO sync_user;

3. Connection Details

You’ll need:

• Hostname: Found in Cloud SQL console (public IP address, or use Cloud SQL Proxy)
• Port: 5432
• Database: Your database name
• Username: sync_user
• Password: The password you set
• SSL: true (recommended)

Azure Database for PostgreSQL

For existing Azure Database for PostgreSQL instances, you’ll need to configure firewall rules and create a user.

1. Configure Firewall Rules

1. Navigate to your Azure Database for PostgreSQL in the Azure Portal
2. Go to Connection security or Networking
3. Add a firewall rule with your Sync or Swim server’s IP address

2. Create Database and User

Connect to your Azure Database using the admin user:

psql "host=your-server.postgres.database.azure.com \
      port=5432 \
      dbname=postgres \
      user=your_admin_user \
      sslmode=require"

Create database and user:

-- Optional: Create database if you don't have one
-- CREATE DATABASE your_database_name;

-- Create user
CREATE USER sync_user WITH PASSWORD 'your_secure_password';

-- Grant privileges (use your database name)
GRANT ALL PRIVILEGES ON DATABASE your_database_name TO sync_user;

-- Connect to the database
\c your_database_name

GRANT ALL ON SCHEMA public TO sync_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO sync_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO sync_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO sync_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO sync_user;

3. Connection Details

You’ll need:

• Hostname: your-server.postgres.database.azure.com
• Port: 5432
• Database: Your database name
• Username: sync_user@your-server (Azure may require this format)
• Password: The password you set
• SSL: true (required by Azure)

Configuring Sync or Swim

Once your PostgreSQL database is set up, configure Sync or Swim with your connection details:

# In your sync_config.yaml or via the UI
services:
  - name: "my_postgres_database"
    type: "postgres"
    credentials:
      hostname: "your-database-host.com"  # or IP address
      port: 5432
      database: "your_database_name"
      username: "sync_user"
      password: "your_secure_password"
      ssl: true  # Set to true for cloud providers, adjust for self-hosted

Connection String Format (Alternative)

Some users prefer connection string format:

postgresql://sync_user:your_secure_password@hostname:5432/your_database_name?sslmode=require

Security Best Practices

1. Use Strong Passwords

• Minimum 16 characters
• Mix of uppercase, lowercase, numbers, and special characters
• Use a password manager
• Rotate passwords regularly

2. Limit Network Access

• Use firewall rules to restrict access to known IP addresses
• For cloud providers, use VPC/Private endpoints when possible
• Avoid 0.0.0.0/0 in production environments

3. Enable SSL/TLS

Always use SSL for connections, especially over the internet:

-- Verify SSL is enforced
SELECT name, setting FROM pg_settings WHERE name = 'ssl';

For cloud providers, SSL is typically required by default.

4. Use Dedicated Users

• Create a specific user for Sync or Swim (don’t use the admin user)
• Grant only necessary privileges
• Use different users for different environments (dev, staging, production)

5. Regular Backups

Self-hosted PostgreSQL:

# Automated backup script
pg_dump -U sync_user -d your_database_name -F c -f backup_$(date +%Y%m%d).dump

Cloud Providers:

• Ensure automated backups are enabled (RDS, Cloud SQL, Azure all support this)
• Verify appropriate retention periods (7-30 days)
• Test restore procedures regularly

6. Monitor Connections

-- View active connections
SELECT pid, usename, application_name, client_addr, state
FROM pg_stat_activity
WHERE datname = 'your_database_name';

Troubleshooting

Connection Refused

Symptoms: could not connect to server: Connection refused

Solutions:

• Verify PostgreSQL is running: sudo systemctl status postgresql
• Check firewall rules allow port 5432
• Verify listen_addresses in postgresql.conf
• Check cloud provider security groups/firewall rules

Authentication Failed

Symptoms: password authentication failed for user

Solutions:

• Verify username and password are correct
• Check pg_hba.conf allows connections from your IP (self-hosted)
• Ensure authentication method is correct (md5, scram-sha-256)
• For cloud providers, verify firewall/security group settings
• For Azure, ensure username format is correct (may need sync_user@servername)

SSL Connection Required

Symptoms: connection requires SSL or sslmode

Solutions:

• Set ssl: true in Sync or Swim configuration
• For cloud providers, SSL is typically required
• Verify SSL certificates are properly configured

Permission Denied

Symptoms: permission denied for schema public or permission denied for table

Solutions:

• Re-run the GRANT statements in the setup section
• Verify user has necessary privileges: \du in psql
• Check ownership of database objects: \dt in psql
• Ensure you connected to the correct database when running GRANT statements

Performance Considerations

For production environments with high synchronization volumes, consider:

Connection Pooling

Sync or Swim uses connection pooling by default. For very high loads, you may want to configure connection pool settings based on your database capacity.

Monitor Performance

-- Check database size
SELECT pg_size_pretty(pg_database_size('your_database_name'));

-- Monitor active queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

-- Check table sizes
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Additional Resources

• PostgreSQL Official Documentation
• Amazon RDS for PostgreSQL
• Google Cloud SQL for PostgreSQL
• Azure Database for PostgreSQL

Support

If you encounter issues not covered in this guide, please contact Sync or Swim support with:

• PostgreSQL version: SELECT version();
• Error messages from the Sync or Swim logs
• Connection details (hostname, port, database name - not passwords!)
• Cloud provider and service tier (if applicable)