Postgre 복원 시 권한 문제 해결 방법SQL 데이터베이스

Postgre 복원 시 권한 문제 해결 방법SQL 데이터베이스

다음 명령을 사용하여 Postgres 데이터베이스에 대한 소유자가 없는 깨끗한 백업을 덤프했습니다.

pg_dump sample_database -O -c -U

나중에 데이터베이스를 복원할 때

psql -d sample_database -U app_name

그러나 다음과 같은 오류가 발생하여 데이터를 복원할 수 없습니다.

ERROR:  must be owner of extension plpgsql
ERROR:  must be owner of schema public
ERROR:  schema "public" already exists
ERROR:  must be owner of schema public
CREATE EXTENSION
ERROR:  must be owner of extension plpgsql

SQL에 .pg_dump하여 SQL이 되어 있는 을 발견했습니다.

CREATE SCHEMA public;
COMMENT ON SCHEMA public IS 'standard public schema';
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

그 원인은 사용자가 사용자가app_name에서는 경할권없다습을 할 수 public 및 스마및plpgsql.

어떻게 하면 이 문제를 해결할 수 있을까요?

문제를 해결하려면 적절한 소유권 권한을 할당해야 합니다.아래를 사용하여 특정 사용자의 모든 권한 관련 문제를 해결할 수 있지만 설명에 명시된 대로 운영 환경에서 사용해서는 안 됩니다.

root@server:/var/log/postgresql# sudo -u postgres psql
psql (8.4.4)
Type "help" for help.

postgres=# \du
               List of roles
    Role name    | Attributes  | Member of
-----------------+-------------+-----------
 <user-name>    | Superuser   | {}
                 : Create DB
 postgres       | Superuser   | {}
                 : Create role
                 : Create DB

postgres=# alter role <user-name> superuser;
ALTER ROLE
postgres=#

계정의 하세요.sudo -u postgres psql합니다.ALTER ROLE <user-name> Superuser;진술.

다중 사이트 호스팅 서버에서는 이 솔루션이 최상의 솔루션이 아니므로 대신 개별 역할(https://www.postgresql.org/docs/current/static/sql-set-role.html 및 https://www.postgresql.org/docs/current/static/sql-alterrole.html )을 할당하는 것을 고려하십시오.

AWS RDS 사용자는 슈퍼 사용자가 아니므로 AWS 설명서에 따르면 슈퍼 사용자가 될 수 없습니다.저는 이러한 오류를 무시해야 한다는 것을 알게 되었습니다.

Google Cloud Platform을 사용하는 사용자의 경우 오류가 발생하면 가져오기 프로세스가 중지됩니다.개인적으로 내가 실행한 pg_dump 명령에 따라 두 가지 다른 오류가 발생했습니다.

1-The input is a PostgreSQL custom-format dump. Use the pg_restore command-line client to restore this dump to a database.

DB를 일반 텍스트 형식으로 덤프하려고 할 때 발생합니다.즉, 명령에 -Fp 또는 --format=dll 매개 변수가 없는 경우입니다.그러나 명령에 추가할 경우 다음 오류가 발생할 수 있습니다.

2-SET SET SET SET SET SET CREATE EXTENSION ERROR: must be owner of extension plpgsql

이는 GCP 문서에 제공된 명령, 현재 스레드의 팁 또는 여기 있는 Google Postgres 팀의 조언을 사용하여 해결할 수 없는 권한 문제입니다.다음 명령을 실행할 것을 권장합니다.

pg_dump -Fp --no-acl --no-owner -U myusername myDBName > mydump.sql

제 경우에는 덤프 파일을 수동으로 편집하고 plpgsql과 관련된 모든 명령을 주석 처리한 것이 유일했습니다.

이것이 GCP에 의존하는 영혼들에게 도움이 되길 바랍니다.

업데이트:

클 수 확장자를 덤프하는 더 . : 특히 일 덤 덤 파 는 더 쉽 다 니 습 것 이 하 프 을 일 표 는 하 시 주 로 부 석 확pg_dump ... | grep -v -E '(CREATE\ EXTENSION|COMMENT\ ON)' > mydump.sql

: 다은 plpgsql 좁힐수있니다습로음▁pl▁which다p▁down있▁be니로 좁혀질 수 있습니다.pg_dump ... | grep -v -E '(CREATE\ EXTENSION\ IF\ NOT\ EXISTS\ plpgsql|COMMENT\ ON\ EXTENSION\ plpgsql)' > mydump.sql

사해보오시를 .-Lpg_restore에서 합니다.pg_dump -Fc

-L list-file --use-list=list-file

목록 파일에 나열된 아카이브 요소만 복원하고 파일에 나타나는 순서대로 복원합니다.-n 또는 -t와 같은 필터링 스위치를 -L과 함께 사용하면 복원된 항목이 더욱 제한됩니다.

list-file은 일반적으로 이전 -l 연산의 출력을 편집하여 생성됩니다.선을 이동하거나 제거할 수 있으며, 선의 시작 부분에 세미콜론(;)을 배치하여 주석을 달 수도 있습니다.예제는 아래를 참조하십시오.

https://www.postgresql.org/docs/9.5/app-pgrestore.html

pg_dump -Fc -f pg.dump db_name
pg_restore -l pg.dump | grep -v 'COMMENT - EXTENSION' > pg_restore.list
pg_restore -L pg_restore.list pg.dump

여기서 설명만 출력하면 Inverse가 참임을 확인할 수 있습니다.

pg_dump -Fc -f pg.dump db_name
pg_restore -l pg.dump | grep 'COMMENT - EXTENSION' > pg_restore_inverse.list
pg_restore -L pg_restore_inverse.list pg.dump
--
-- PostgreSQL database dump
--

-- Dumped from database version 9.4.15
-- Dumped by pg_dump version 9.5.14

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


--
-- PostgreSQL database dump complete
--

이 경우 오류 메시지를 안전하게 무시할 수 있습니다.공개 스키마에 주석을 추가하지 못하고 pplpgsql(이미 설치되어 있어야 함)을 설치해도 실제 문제가 발생하지 않습니다.

그러나 전체 재설치를 수행하려면 적절한 권한을 가진 사용자가 필요합니다.물론 해당 사용자가 애플리케이션을 일상적으로 실행하는 사용자일 수는 없습니다.

더 짧은 대답: 무시하세요.

이 모듈은 Postgres에서 SQL 언어를 처리하는 부분입니다.이 오류는 'herokupg:pull'과 같이 원격 데이터베이스를 복사할 때 자주 나타납니다.SQL 프로세서를 덮어쓰지 않고 경고합니다.

AWS를 사용하는 사용자의 경우COMMENT ON EXTENSIONSuperuser로만 가능하며, 문서에서 알 수 있듯이 RDS 인스턴스는 Amazon에서 관리합니다.따라서 복제와 같은 문제를 방지하기 위해 사용자는 물론 인스턴스를 만들 때 설정한 루트 사용자도 다음과 같은 완전한 슈퍼 사용자 권한을 갖지 않습니다.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html

DB 인스턴스를 생성하면 생성한 마스터 사용자 시스템 계정이 rds_superuser 역할에 할당됩니다.rds_superuser 역할은 Postgre와 유사한 미리 정의된 Amazon RDS 역할입니다.SQL superuser 역할(로컬 인스턴스에서 사용자 지정 이름이 postgres)이지만 일부 제한이 있습니다.포스트그레와 마찬가지로SQL superuser 역할인 rds_superuser 역할은 DB 인스턴스에 대한 가장 많은 권한을 가지고 있으며 DB 인스턴스에 대한 가장 많은 액세스 권한이 필요한 사용자가 아니면 이 역할을 할당하지 않아야 합니다.

이 오류를 수정하려면 다음을 사용하십시오.--다음을 포함하는 SQL 행을 주석 처리합니다.COMMENT ON EXTENSION

EDIT 1: 드미트리 I.가 제안한 것처럼, 다음을 덤핑할 때 주석을 생략할 수도 있습니다.pg_dump --no-comments

문제를 다음으로 좁힌 사람들을 위해.COMMENT ON아래의 다양한 답변에 따라 덤프 파일이 생성된 원본 데이터베이스에 대한 슈퍼유저 액세스 권한을 가진 사용자는 덤프되는 원본 데이터베이스에서 의견을 제거하여 의견이 덤프 파일에 포함되지 않도록 하는 것이 가장 간단한 해결책일 수 있습니다.

COMMENT ON EXTENSION postgis IS NULL;
COMMENT ON EXTENSION plpgsql IS NULL;
COMMENT ON SCHEMA public IS NULL;

그러면 미래 덤프는 포함되지 않습니다.COMMENT ON진술들.

복원하기 전에 postgres(admin) 사용자를 사용하여 스키마를 덤프하고 다시 만들고 사용할 권한을 부여합니다.단일 명령:

sudo -u postgres psql -c "DROP SCHEMA public CASCADE;
create SCHEMA public;
grant usage on schema public to public;
grant create on schema public to public;" myDBName

저는 pgAdmin으로 데이터베이스를 설정하고 있었는데 데이터베이스 생성 중에 소유자를 설정하는 것만으로는 충분하지 않은 것 같습니다.저는 '공개' 스키마로 이동하여 소유자를 그곳에 설정해야 했습니다(원래는 '포스트그레스'였습니다).

일부 답변에서는 확장 작성 및 확장에 대한 설명을 제거하는 것과 관련된 다양한 접근 방식을 이미 제공했습니다.다음 명령줄이 문제를 해결하는 가장 간단한 방법인 것 같습니다.

cat /tmp/backup.sql.gz | gunzip - | \
  grep -v -E '(CREATE\ EXTENSION|COMMENT\ ON)' |  \
    psql --set ON_ERROR_STOP=on -U db_user -h localhost my_db

일부 참고 사항

• 첫 번째 줄은 백업의 압축을 푸는 것이며 그에 따라 조정해야 할 수도 있습니다.
• 두 번째 줄은 grep을 사용하여 위반하는 줄을 제거하는 것입니다.
• 세 번째 줄은 my psql 명령입니다. 일반적으로 복원에 psql을 사용하는 것처럼 조정해야 할 수도 있습니다.

언급URL : https://stackoverflow.com/questions/13410631/how-to-solve-privileges-issues-when-restore-postgresql-database