CODEWITHSUNDEEP
postgresqlpsqlpg-dump
Anton Patsev
Anton Patsev

Reputation: 639

How backup and restore only list(name) databases, roles and right for databases in postgresql without data?

How backup and restore only list(name) databases, roles and right for databases in postgresql without data?

Bash script for get list database postgresql

sudo -i -u postgres psql  -t -A -c 'SELECT datname FROM pg_database'

But how restore databases and relationship for roles and databases in postgresql?

Vao Tsun answer:

to prepare sql with database list for creation use smth like: psql -c "select 'create database '||datname||';' from pg_database where not datistemplate and datname != 'postgres';" -At >cdbs.sql, to create database from a list, use psql -f cdbs.sql against new cluster

But all restored databases have owner postgres. How restore databases with owner? Thanks

Upvotes: 1

Views: 1117

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51559

from top of the head - try:

  1. to get all roles, use pg_dumpall -g
  2. to get permissions use pg_dump -s >to_file.sql and then psql -f to_file.sql ignoring creations errors.
  3. to prepare sql with database list for creation use smth like: psql -c "select 'create database '||datname||';' from pg_database where not datistemplate and datname != 'postgres';" -At >cdbs.sql, to create database from a list, use psql -f cdbs.sql against new cluster

Upvotes: 0

Related Questions