In practical database administration scenarios, there are legitimate cases where you might need a single PostgreSQL database to respond to multiple names. This could be for backward compatibility with legacy applications, testing environments, or migration scenarios where multiple application instances need to point to the same logical database but use different connection strings.

Unlike MySQL which allows symbolic links at the filesystem level, PostgreSQL takes a different architectural approach to database naming. Here are the most practical solutions:

-- Option 1: Using search_path in a session
SET search_path TO db_alpha, public;
-- Now unqualified objects will first look in db_alpha

The most robust method is to use PostgreSQL's schema feature:

-- Create your main schema
CREATE SCHEMA db_alpha;

-- Create a synonym schema that points to the same tables
CREATE SCHEMA db_one;
CREATE VIEW db_one.table1 AS SELECT * FROM db_alpha.table1;
-- Repeat for all tables

For true database-level aliasing, consider these approaches:

-- Option 1: Using foreign data wrappers
CREATE EXTENSION postgres_fdw;
CREATE SERVER db_one FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'localhost', dbname 'db_alpha');

-- Option 2: Connection pooling with PgBouncer
; Configure in pgbouncer.ini
[databases]
db_alpha = host=127.0.0.1 port=5432 dbname=db_alpha
db_one = host=127.0.0.1 port=5432 dbname=db_alpha

Sometimes the simplest solution is at the application level:

# Python example with SQLAlchemy
from sqlalchemy import create_engine

# Both connection strings point to the same database
eng1 = create_engine('postgresql://user:pass@localhost/db_alpha')
eng2 = create_engine('postgresql://user:pass@localhost/db_one')

Each approach has different performance characteristics. Schema-based solutions add minimal overhead, while FDW solutions may impact query performance. Always test with your specific workload.

When working with PostgreSQL, you might encounter scenarios where a single database needs to be accessible through multiple names. This could be for backward compatibility, testing environments, or application requirements where different components expect different database names.

Unlike MySQL which uses a simple filesystem-based approach allowing symbolic links, PostgreSQL implements a more robust system with its own catalog and permission structures. The database name is tightly coupled with internal identifiers and system catalogs.

1. Using Schema-Level Aliasing

The closest PostgreSQL equivalent would be to use schemas within a single database:

-- Create a database with your primary name
CREATE DATABASE db_alpha;

-- Connect to db_alpha and create a schema matching your alias name
\c db_alpha
CREATE SCHEMA db_one;

-- Set search_path to include both
ALTER DATABASE db_alpha SET search_path = "$user", public, db_one;

2. Connection Pooling with Name Mapping

Tools like PgBouncer can be configured to map connection requests:

[databases]
db_alpha = host=127.0.0.1 port=5432 dbname=db_alpha
db_one = host=127.0.0.1 port=5432 dbname=db_alpha

3. DNS-Level Solution

For applications connecting via hostname, you can create DNS CNAME records that point to the same PostgreSQL server, then use connection strings with different hostnames that ultimately resolve to the same database.

While not exactly aliasing the entire database, you can create views that make objects appear to be in multiple namespaces:

-- In the public schema of db_alpha
CREATE VIEW db_one.some_table AS SELECT * FROM public.some_table;

• Permissions need to be managed carefully when using schema-based approaches
• Connection pooling solutions add complexity to your infrastructure
• Some PostgreSQL tools may still reference the original database name in logs