MySQL 은 SELECT * FROM otherdb.tbl 처럼 데이터베이스 이름을 앞에 붙여 다른 데이터베이스를 바로 읽을 수 있다. PostgreSQL 은 그렇지 않다. 하나의 연결은 하나의 데이터베이스에 묶이며, database.schema.table 형태의 3단계 이름은 현재 데이터베이스를 가리킬 때만 문법적으로 허용된다.
-- appdb 에 접속한 상태에서
SELECT * FROM appdb.app.users; -- 된다. 현재 DB 이므로
SELECT * FROM otherdb.app.users; -- 오류. cross-database references are not implemented
즉 "database 는 sync, table 은 users" 라는 조건만으로 쿼리를 쓸 수 없다. 먼저 그 데이터베이스에 접속해야 한다.
psql -h dbhost -U appuser -d sync -c "SELECT * FROM public.users LIMIT 10"
psql 안에서는 \c sync 로 옮긴다. 연결이 새로 맺어지므로 임시 테이블과 세션 변수는 사라진다.
같은 데이터베이스 안에서는 스키마 이름으로 구분한다.
SELECT * FROM app.users;
스키마를 생략하면 search_path 순서로 찾는다.
SHOW search_path; -- 보통 "$user", public
SET search_path TO app, public;
세션 단위 설정이므로 접속할 때마다 초기화된다. 롤이나 데이터베이스에 고정하려면 다음과 같이 둔다.
ALTER ROLE appuser IN DATABASE appdb SET search_path TO app, public;
같은 서버든 다른 서버든 외부 테이블로 끌어와 조인할 수 있다. 현재 권장되는 방법이다.
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER sync_srv
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', port '5432', dbname 'sync');
CREATE USER MAPPING FOR appuser
SERVER sync_srv
OPTIONS (user 'appuser', password '${PASSWORD}');
CREATE SCHEMA sync_remote;
IMPORT FOREIGN SCHEMA public LIMIT TO (users)
FROM SERVER sync_srv INTO sync_remote;
SELECT * FROM sync_remote.users;
조인 조건과 집계가 원격으로 내려갈 수 있는지(EXPLAIN 의 Remote SQL)를 확인한다. 내려가지 않으면 전체를 끌어와 로컬에서 처리하므로 느려진다.
한 번씩 쿼리를 던지는 용도라면 dblink 가 간단하다. 다만 결과 컬럼 타입을 매번 적어야 한다.
CREATE EXTENSION IF NOT EXISTS dblink;
SELECT * FROM dblink('dbname=sync', 'SELECT id, name FROM users')
AS t(id bigint, name text);