PostgreSQL Installation

PostgreSQL is a powerful, advanced open-source relational database with excellent support for complex queries, JSON data, and spatial operations. This guide covers the complete installation process for the Countries States Cities database.

PostgreSQL installation typically takes 3-6 minutes and requires approximately 220MB of disk space.

Prerequisites

Before starting, ensure you have:

PostgreSQL 10+ installed (PostgreSQL 13+ recommended)
Administrative access to your PostgreSQL server
At least 500MB free disk space
Downloaded the pgsql/world.sql file from our GitHub repository

Method 1: Command Line Import

Create Database

createdb -U postgres world

Or using psql:

psql -U postgres -c "CREATE DATABASE world WITH ENCODING='UTF8';"

PostgreSQL uses UTF-8 encoding by default, which properly handles all international characters.

Import Database

psql -U postgres -d world -f pgsql/world.sql

PostgreSQL import includes automatic index creation for optimal performance.

Verify Installation

psql -U postgres -d world -c "
SELECT 'countries' as table_name, COUNT(*) as count FROM countries
UNION ALL
SELECT 'states', COUNT(*) FROM states
UNION ALL  
SELECT 'cities', COUNT(*) FROM cities;
"

Expected output:

 table_name | count  
------------|--------
 countries  |   250
 states     |  5038
 cities     | 151024

Method 2: pgAdmin GUI

For users who prefer a graphical interface:

Connect to Server

Open pgAdmin and connect to your PostgreSQL server.

Create Database

Right-click on “Databases”
Select “Create” → “Database”
Name: “world”
Encoding: “UTF8”
Template: “template0”

Import SQL File

Right-click on the “world” database
Select “Query Tool”
Click the folder icon to open file
Select pgsql/world.sql
Execute the script (F5)

PostgreSQL-Specific Features

Spatial Queries with PostGIS

Enable PostGIS Extension

-- Enable PostGIS for advanced spatial queries (optional)
CREATE EXTENSION IF NOT EXISTS postgis;

Create Spatial Indexes

-- Add geometry columns for spatial operations
ALTER TABLE cities ADD COLUMN geom geometry(POINT, 4326);
UPDATE cities SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

-- Create spatial index
CREATE INDEX idx_cities_geom ON cities USING GIST(geom);

Example Spatial Queries

-- Find cities within 100km of New York City
SELECT name, country_name, latitude, longitude 
FROM cities 
WHERE ST_DWithin(
  geom,
  ST_SetSRID(ST_MakePoint(-74.006, 40.7128), 4326)::geography,
  100000  -- 100km in meters
);

-- Find nearest cities to a point
SELECT name, country_name,
       ST_Distance(geom, ST_SetSRID(ST_MakePoint(-74.006, 40.7128), 4326)::geography) as distance
FROM cities 
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(-74.006, 40.7128), 4326)
LIMIT 10;

Full-Text Search

PostgreSQL provides powerful full-text search capabilities:

-- Create full-text search indexes
CREATE INDEX idx_countries_name_fts ON countries USING gin(to_tsvector('english', name));
CREATE INDEX idx_cities_name_fts ON cities USING gin(to_tsvector('english', name));
CREATE INDEX idx_states_name_fts ON states USING gin(to_tsvector('english', name));

Advanced Configuration

Create Application User and Database

Create Role

CREATE ROLE csc_user WITH LOGIN PASSWORD 'secure_password_here';

Replace ‘secure_password_here’ with a strong, unique password.

Grant Permissions

-- Grant database access
GRANT CONNECT ON DATABASE world TO csc_user;

-- Grant schema usage
GRANT USAGE ON SCHEMA public TO csc_user;

-- Grant table permissions
GRANT SELECT ON ALL TABLES IN SCHEMA public TO csc_user;

-- Grant permissions on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO csc_user;

Performance Optimization

-- Performance indexes for common queries
CREATE INDEX idx_countries_iso2 ON countries(iso2);
CREATE INDEX idx_countries_iso3 ON countries(iso3);
CREATE INDEX idx_countries_region ON countries(region);
CREATE INDEX idx_countries_subregion ON countries(subregion);

CREATE INDEX idx_states_country_id ON states(country_id);
CREATE INDEX idx_states_name ON states(name);
CREATE INDEX idx_states_country_name ON states(country_name);

CREATE INDEX idx_cities_country_id ON cities(country_id);
CREATE INDEX idx_cities_state_id ON cities(state_id);
CREATE INDEX idx_cities_name ON cities(name);
CREATE INDEX idx_cities_coordinates ON cities(latitude, longitude);
CREATE INDEX idx_cities_country_name ON cities(country_name);

Database Structure Verification

-- List all tables
\dt

-- Describe table structures
\d countries
\d states  
\d cities

-- Check table sizes
SELECT 
    schemaname as schema,
    tablename as table,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
    pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) as index_size
FROM pg_tables 
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Connection Examples

Python (psycopg2)
Node.js (pg)
Java (JDBC)

import psycopg2
from psycopg2.extras import RealDictCursor

config = {
    'host': 'localhost',
    'database': 'world',
    'user': 'csc_user',
    'password': 'your_password',
    'port': 5432
}

try:
    connection = psycopg2.connect(**config)
    cursor = connection.cursor(cursor_factory=RealDictCursor)
    
    # Test query
    cursor.execute("SELECT COUNT(*) as count FROM countries")
    result = cursor.fetchone()
    print(f"Countries in database: {result['count']}")
    
    # Example query with parameters
    cursor.execute("""
        SELECT name, latitude, longitude 
        FROM cities 
        WHERE country_name = %s 
        LIMIT 5
    """, ('United States',))
    
    cities = cursor.fetchall()
    for city in cities:
        print(f"{city['name']}: {city['latitude']}, {city['longitude']}")
        
except psycopg2.Error as error:
    print(f"Database error: {error}")
    
finally:
    if connection:
        cursor.close()
        connection.close()

const { Pool } = require('pg');

const pool = new Pool({
  user: 'csc_user',
  host: 'localhost',
  database: 'world',
  password: 'your_password',
  port: 5432,
});

async function testConnection() {
  const client = await pool.connect();
  
  try {
    // Test query
    const result = await client.query('SELECT COUNT(*) as count FROM countries');
    console.log(`Countries in database: ${result.rows[0].count}`);
    
    // Example parameterized query
    const cities = await client.query(
      'SELECT name, latitude, longitude FROM cities WHERE country_name = $1 LIMIT 5',
      ['United States']
    );
    
    cities.rows.forEach(city => {
      console.log(`${city.name}: ${city.latitude}, ${city.longitude}`);
    });
    
  } catch (err) {
    console.error('Database error:', err.message);
  } finally {
    client.release();
  }
}

testConnection();

import java.sql.*;
import java.util.Properties;

public class PostgreSQLConnection {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/world";
        Properties props = new Properties();
        props.setProperty("user", "csc_user");
        props.setProperty("password", "your_password");
        
        try (Connection conn = DriverManager.getConnection(url, props)) {
            // Test query
            try (PreparedStatement stmt = conn.prepareStatement("SELECT COUNT(*) FROM countries")) {
                ResultSet rs = stmt.executeQuery();
                if (rs.next()) {
                    System.out.println("Countries in database: " + rs.getInt(1));
                }
            }
            
            // Example query with parameters
            String query = "SELECT name, latitude, longitude FROM cities WHERE country_name = ? LIMIT 5";
            try (PreparedStatement stmt = conn.prepareStatement(query)) {
                stmt.setString(1, "United States");
                ResultSet rs = stmt.executeQuery();
                
                while (rs.next()) {
                    System.out.printf("%s: %f, %f%n", 
                        rs.getString("name"), 
                        rs.getDouble("latitude"), 
                        rs.getDouble("longitude"));
                }
            }
            
        } catch (SQLException e) {
            System.err.println("Database error: " + e.getMessage());
        }
    }
}

JSON Support

PostgreSQL has excellent JSON support. Here are some examples:

-- Aggregate cities by country as JSON
SELECT country_name, json_agg(
  json_build_object(
    'name', name,
    'latitude', latitude,
    'longitude', longitude
  )
) as cities
FROM cities 
WHERE country_name = 'Switzerland'
GROUP BY country_name;

Troubleshooting

Connection Refused Error

Problem: could not connect to server: Connection refusedSolutions:

Check if PostgreSQL service is running: sudo systemctl status postgresql
Verify PostgreSQL is listening on correct port: netstat -an | grep 5432
Check pg_hba.conf for connection permissions
Ensure firewall allows connections on port 5432

Authentication Failed

Problem: FATAL: password authentication failedSolutions:

Verify username and password are correct
Check pg_hba.conf authentication method
Reset password: ALTER USER username PASSWORD 'newpassword';
Ensure user has login privileges: ALTER USER username LOGIN;

Permission Denied

Problem: ERROR: permission denied for table countriesSolutions:

Grant table permissions: GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;
Check current permissions: \dp in psql
Ensure user has schema usage: GRANT USAGE ON SCHEMA public TO username;
Grant database connection: GRANT CONNECT ON DATABASE world TO username;

Slow Query Performance

Problem: Queries are running slowlySolutions:

Add appropriate indexes (see Performance Optimization section)
Update table statistics: ANALYZE;
Check query plan: EXPLAIN ANALYZE your_query;
Increase shared_buffers in postgresql.conf
Consider using connection pooling (pgbouncer)

Backup and Maintenance

Create Backup Script

#!/bin/bash
# backup_world_db.sh

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/path/to/backups"
DB_NAME="world"

# Create compressed backup
pg_dump -U postgres -h localhost $DB_NAME | gzip > "$BACKUP_DIR/world_backup_$DATE.sql.gz"

# Create custom format backup (faster restore)
pg_dump -U postgres -h localhost -Fc $DB_NAME > "$BACKUP_DIR/world_backup_$DATE.dump"

# Keep only last 7 backups
find $BACKUP_DIR -name "world_backup_*.sql.gz" -type f -mtime +7 -delete
find $BACKUP_DIR -name "world_backup_*.dump" -type f -mtime +7 -delete

echo "Backup completed: world_backup_$DATE"

Regular Maintenance

-- Vacuum and analyze tables
VACUUM ANALYZE countries, states, cities;

-- Check database statistics
SELECT schemaname, tablename, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
FROM pg_stat_user_tables 
WHERE schemaname = 'public';

-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes 
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

PostgreSQL’s autovacuum process handles most maintenance automatically, but manual VACUUM ANALYZE can help after large data changes.

Next Steps

After successful installation:

Configure connection pooling with pgbouncer for production
Set up monitoring with pg_stat_monitor or similar tools
Implement backup strategy using the provided script
Explore spatial features with PostGIS extension
Optimize queries using EXPLAIN ANALYZE

Need Help?

Join our community discussions for PostgreSQL-specific questions and advanced optimization tips.

Database Documentation

Installation Guides

Support

Prerequisites

Method 1: Command Line Import

Method 2: pgAdmin GUI

PostgreSQL-Specific Features

Spatial Queries with PostGIS

Full-Text Search

Advanced Configuration

Create Application User and Database

Performance Optimization

Database Structure Verification

Connection Examples

JSON Support

Troubleshooting

Backup and Maintenance

Create Backup Script

Regular Maintenance

Next Steps

Need Help?

Database Documentation

Installation Guides

Support

​Prerequisites

​Method 1: Command Line Import

​Method 2: pgAdmin GUI

​PostgreSQL-Specific Features

​Spatial Queries with PostGIS

​Full-Text Search

​Advanced Configuration

​Create Application User and Database

​Performance Optimization

​Database Structure Verification

​Connection Examples

​JSON Support

​Troubleshooting

​Backup and Maintenance

​Create Backup Script

​Regular Maintenance

​Next Steps

Need Help?

Prerequisites

Method 1: Command Line Import

Method 2: pgAdmin GUI

PostgreSQL-Specific Features

Spatial Queries with PostGIS

Full-Text Search

Advanced Configuration

Create Application User and Database

Performance Optimization

Database Structure Verification

Connection Examples

JSON Support

Troubleshooting

Backup and Maintenance

Create Backup Script

Regular Maintenance

Next Steps