Skip to main content
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

1

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.
2

Import Database

psql -U postgres -d world -f pgsql/world.sql
PostgreSQL import includes automatic index creation for optimal performance.
3

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:
1

Connect to Server

Open pgAdmin and connect to your PostgreSQL server.
2

Create Database

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

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

1

Enable PostGIS Extension

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

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);
3

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

1

Create Role

CREATE ROLE csc_user WITH LOGIN PASSWORD 'secure_password_here';
Replace ‘secure_password_here’ with a strong, unique password.
2

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()

​
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

Problem: could not connect to server: Connection refusedSolutions:
  1. Check if PostgreSQL service is running: sudo systemctl status postgresql
  2. Verify PostgreSQL is listening on correct port: netstat -an | grep 5432
  3. Check pg_hba.conf for connection permissions
  4. Ensure firewall allows connections on port 5432
Problem: FATAL: password authentication failedSolutions:
  1. Verify username and password are correct
  2. Check pg_hba.conf authentication method
  3. Reset password: ALTER USER username PASSWORD 'newpassword';
  4. Ensure user has login privileges: ALTER USER username LOGIN;
Problem: ERROR: permission denied for table countriesSolutions:
  1. Grant table permissions: GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;
  2. Check current permissions: \dp in psql
  3. Ensure user has schema usage: GRANT USAGE ON SCHEMA public TO username;
  4. Grant database connection: GRANT CONNECT ON DATABASE world TO username;
Problem: Queries are running slowlySolutions:
  1. Add appropriate indexes (see Performance Optimization section)
  2. Update table statistics: ANALYZE;
  3. Check query plan: EXPLAIN ANALYZE your_query;
  4. Increase shared_buffers in postgresql.conf
  5. 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:
  1. Configure connection pooling with pgbouncer for production
  2. Set up monitoring with pg_stat_monitor or similar tools
  3. Implement backup strategy using the provided script
  4. Explore spatial features with PostGIS extension
  5. Optimize queries using EXPLAIN ANALYZE

Need Help?

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