PostgreSQL 데이터베이스 복원시 권한 문제를 해결하는 방법

PostgreSQL 데이터베이스 복원시 권한 문제를 해결하는 방법

---

명령을 사용하여 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 = plain 매개 변수가없는 경우. 그러나 명령에 추가하면 다음 오류가 발생할 수 있습니다.

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로 좁힐 수 있습니다. pg_dump ... | grep -v -E '(CREATE\ EXTENSION\ IF\ NOT\ EXISTS\ plpgsql|COMMENT\ ON\ EXTENSION\ plpgsql)' > mydump.sql

---

You can probably safely ignore the error messages in this case. Failing to add a comment to the public schema and installing plpgsql (which should already be installed) aren't going to cause any real problems.

However, if you want to do a complete re-install you'll need a user with appropriate permissions. That shouldn't be the user your application routinely runs as of course.

---

Shorter answer: ignore it.

This module is the part of Postgres that processes the SQL language. The error will often pop up as part of copying a remote database, such as with a 'heroku pg:pull'. It does not overwrite your SQL processor and warns you about that.

---

Try using the -L flag with pg_restore by specifying the file taken from pg_dump -Fc

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

Restore only those archive elements that are listed in list-file, and restore them in the order they appear in the file. Note that if filtering switches such as -n or -t are used with -L, they will further restrict the items restored.

list-file is normally created by editing the output of a previous -l operation. Lines can be moved or removed, and can also be commented out by placing a semicolon (;) at the start of the line. See below for examples.

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

Here you can see the Inverse is true by outputting only the comment:

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

---

Use the postgres (admin) user to dump the schema, recreate it and grant priviledges for use before you do your restore. In one command:

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

---

For me, I was setting up a database with pgAdmin and it seems setting the owner during database creation was not enough. I had to navigate down to the 'public' schema and set the owner there as well (was originally 'postgres').

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