Re: Version upgrade: is restoring the postgres database needed?
От | Ron Johnson |
---|---|
Тема | Re: Version upgrade: is restoring the postgres database needed? |
Дата | |
Msg-id | e220bd99-d9ad-3326-0589-6aec1c6eff57@cox.net обсуждение исходный текст |
Ответ на | Version upgrade: is restoring the postgres database needed? (Ron Johnson <ron.l.johnson@cox.net>) |
Ответы |
Re: Version upgrade: is restoring the postgres database needed?
Re: Version upgrade: is restoring the postgres database needed? |
Список | pgsql-general |
On 03/01/2018 11:03 AM, Melvin Davidson wrote:
On Thu, Mar 1, 2018 at 11:51 AM, Ron Johnson <ron.l.johnson@cox.net> wrote:On 03/01/2018 10:37 AM, Vick Khera wrote:On Thu, Mar 1, 2018 at 11:15 AM, Ron Johnson <ron.l.johnson@cox.net> wrote:No, I do:
$ pg_dump -Fc PROD > PROD.pgdump
$ pg_dump --globals-only postgres > globals.sql
$ pg_dump -Fc postgres > postgres.pgdumpThat's how I back them up as well. You are correct that all you need to do is restore the globals.sql, then each "pgdump" file individually. Just ignore the warning when it tries to restore your initial postgres superuser, since it was created by the initdb already.You probably don't need the "postgres" db at all, since it is just there to allow the client to connect to something on initial install. Normally you don't use it in production.
Good. What, then, have I forgotten to restore such that the "Access privileges" are showing on my current 9.2 servers, but not on the newly-restored 9.6.6 server?
Current
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+----------+----------+-------------+---------- ---+-----------------------
CSSCAT_STI | CSS | UTF8 | en_US.UTF-8 | en_US.UTF-8 | CSS=CTc/CSS +
| | | | | =Tc/CSS +
| | | | | app_user=CTc/CSS
CSSCAT_STIB | CSS | UTF8 | en_US.UTF-8 | en_US.UTF-8 | CSS=CTc/CSS +
| | | | | =Tc/CSS +
| | | | | app_user=CTc/CSS
CSSCAT_STIC | CSS | UTF8 | en_US.UTF-8 | en_US.UTF-8 | CSS=CTc/CSS +
| | | | | =Tc/CSS +
| | | | | app_user=CTc/CSS
Newly restored
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+----------+----------+-------------+---------- ---+-----------------------
CSSCAT_STIB | CSS | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
CSSCAT_STIC | CSS | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |--
Angular momentum makes the world go 'round.
>$ pg_dump -Fc PROD > PROD.pgdump
>$ pg_dump --globals-only postgres > globals.sql
>$ pg_dump -Fc postgres > postgres.pgdumpThe last I looked, pg_dump does not have a "--globals-only"Did you mean?
$ pg_dump -Fc PROD > PROD.pgdump
$ pg_dumpall --globals-only postgres > globals.sql
OR
$ pg_dumpall -g > globals.sql
$ pg_dump -Fc postgres > postgres.pgdump
Hmmm. I just looked at the script, and it says:
$ pg_dumpall --schema-only > globals.sql
That's not good.
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
В списке pgsql-general по дате отправления: