Sql Postgresql

PostgreSQL에서 크로스 데이터베이스 쿼리를 수행할 수 있나요? 방법은 다음과 같습니다!

PostgreSQL을 사용하고 있다면, 크로스 데이터베이스 쿼리를 수행할 수 있는지 궁금할 수 있습니다. 이는 일반적으로 서로 다른 데이터베이스에 분할된 데이터에 접근하거나 조작해야 할 때 발생합니다. 두 개의 데이터베이스가 데이터를 공유하지만 별도로 설정된 경우가 일반적인 상황입니다. 이로 인해 다음과 같은 질문이 생깁니다: PostgreSQL에서 한 데이터베이스에서 다른 데이터를 쿼리할 수 있나요?

크로스 데이터베이스 쿼리의 도전과제

크로스 데이터베이스 쿼리를 실행하려고 할 때 다음과 유사한 오류 메시지를 접할 수 있습니다:

databaseA=# select * from databaseB.public.someTableName;
ERROR:  cross-database references are not implemented: "databaseB.public.someTableName"

이 오류는 PostgreSQL이 데이터베이스 간의 직접적인 참조를 본래 지원하지 않음을 나타냅니다. 따라서 데이터가 별도의 데이터베이스에 저장되어 있고 이를 넘나들며 쿼리를 수행해야 한다면, 대안을 찾아야 합니다.

해결책: 외부 데이터 래퍼 사용하기

PostgreSQL에서 크로스 데이터베이스 쿼리를 수행하려면 외부 데이터 래퍼 기능, 특히 postgres_fdw를 활용할 수 있습니다. 이를 통해 로컬 또는 원격의 모든 PostgreSQL 데이터베이스에 있는 테이블에 쉽게 연결할 수 있습니다.

postgres_fdw 설정하기

  1. 확장 모듈 설치: PostgreSQL 설치에서 postgres_fdw 확장이 활성화되어 있는지 확인하세요. 다음 명령어로 확장을 생성할 수 있습니다:

    CREATE EXTENSION postgres_fdw;

  2. 외부 서버 정의하기: 대상 데이터베이스에 연결되는 외부 서버를 생성합니다:

    CREATE SERVER foreign_server_name
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'host_address', dbname 'databaseB', port '5432');

  3. 사용자 매핑 생성하기: 외부 서버에 연결할 사용자의 매핑을 생성해야 합니다:

    CREATE USER MAPPING FOR local_user
SERVER foreign_server_name
OPTIONS (user 'remote_user', password 'remote_password');

  4. 외부 스키마 가져오기: 접근하려는 테이블을 포함하는 외부 스키마를 가져올 수 있습니다:

    IMPORT FOREIGN SCHEMA public
FROM SERVER foreign_server_name
INTO local_schema;

  5. 원격 테이블 쿼리하기: 이제 외부 테이블을 일반 로컬 테이블처럼 쿼리할 수 있습니다:

    SELECT * FROM local_schema.some_table_name;

구버전 PostgreSQL을 위한 방법

PostgreSQL 9.3 이전 버전을 사용 중이라면 외부 데이터 래퍼 기능이 지원되지 않습니다. 그러나, 다른 PostgreSQL 데이터베이스에 연결할 수 있는 dblink 함수를 사용할 수 있습니다.

  • dblink 사용하기: 배포판에 포함되지 않았다면 postgresql-contrib 패키지를 설치해야 합니다. 그 후 다음과 같은 쿼리를 실행할 수 있습니다: 
    SELECT * FROM dblink('dbname=databaseB user=remote_user password=remote_password',
                    'SELECT * FROM public.some_table_name') AS t(column1 type1, column2 type2, ...);

노트: 가능하다면 데이터베이스 구조를 재구성하는 것을 고려하세요. 별도의 데이터베이스 대신 동일한 데이터베이스 내에서 스키마를 사용하는 것이 데이터 관리 및 접근성을 크게 향상시킬 수 있습니다.

결론

PostgreSQL이 크로스 데이터베이스 쿼리를 본래 지원하지는 않지만, postgres_fdw와 같은 외부 데이터 래퍼를 사용하면 강력한 우회 방법이 제공됩니다. 필요한 설정을 진행하면 서로 다른 데이터베이스 간에 데이터를 원활하게 액세스하고 조작할 수 있습니다.

구버전을 사용하고 있다면 dblink가 옵션이지만, 최신 PostgreSQL 버전으로 업그레이드하면 더 나은 기능과 지원을 받을 수 있습니다.

올바른 도구와 전략을 가지고 데이터베이스 간 데이터에 접근하는 것은 그 어느 때보다 쉬워졌습니다. 쿼리 작성 즐기세요!