Backup & Restore Database in PostgreSQL (pg_dump,pg_restore)

1ª Forma:

Published: July 16, 2008 , Updated: May 31, 2010 , Author: mkyong

print

Here i demostrate how to backup and restore dabatase in PostgreSQL
1)Backup data with pg_dump

pg_dump -i -h localhost -p 5432 -U postgres -F c -b -v -f 
"/usr/local/backup/10.70.0.61.backup" old_db

To list all of the available options of pg_dump , please issue following command.

1	pg_dump -?

-p, –port=PORT database server port number
-i, –ignore-version proceed even when server version mismatches
-h, –host=HOSTNAME database server host or socket directory
-U, –username=NAME connect as specified database user
-W, –password force password prompt (should happen automatically)
-d, –dbname=NAME connect to database name
-v, –verbose verbose mode
-F, –format=c|t|p output file format (custom, tar, plain text)
-c, –clean clean (drop) schema prior to create
-b, –blobs include large objects in dump
-v, –verbose verbose mode
-f, –file=FILENAME output file name

2) Restore data with pg_restore

pg_restore -i -h localhost -p 5432 -U postgres -d old_db -v 
"/usr/local/backup/10.70.0.61.backup"

To list all of the available options of pg_restore , please issue following command.

1	pg_restore -?

-p, –port=PORT database server port number
-i, –ignore-version proceed even when server version mismatches
-h, –host=HOSTNAME database server host or socket directory
-U, –username=NAME connect as specified database user
-W, –password force password prompt (should happen automatically)
-d, –dbname=NAME connect to database name
-v, –verbose verbose mode


Criar usuário administrador no bando de dados:

 createuser -a -d -E -P nomeusuario

Codificação LATIN


2ª Forma

#Backup
pg_dump -vW -Upostgres dbname | gzip > /home/bkp_banco.gz


#Restore
cat bkp_banco.gz | gunzip | psql dbname


---------------------------------------------------------------------------------------------------------

Comandos Postgresql:

Criar Usuário
CREATE ROLE nomeusuario;

Nas versões anteriores usava-se o parâmetro “CREATEUSER” para indicar a criação de um superusuário, agora usa-se o parâmetro mais adequado SUPERUSER.

Para poder criar um novo usuário local, com senha, devemos setar antes o pg_hba.conf:
local all all 127.0.0.1/32 password
Comentar as outras entradas para conexão local.
Isso para usuário local (conexão via socket UNIX).

Criamos assim:
CREATE ROLE nomeuser WITH ENCRYPTED PASSWORD '********';
Ao se logar: psql -U nomeuser nomebanco.
CREATE ROLE nomeusuario VALID UNTIL 'data'

Excluindo Usuário
DROP USER nomeusuario;

Como usuário, fora do psql:

Criar Usuário
CREATEROLE nomeusuario;

Excluindo Usuário
DROPUSER nomeusuario;
Detalhe: sem espaços.

Criando Superusuário
CREATE ROLE nomeuser WITH SUPERUSER ENCRYPTED PASSWORD '******';
-- usuário com poderes de super usuário

Alterar Conta de Usuário
ALTER ROLE nomeuser ENCRYPTED PASSWORD '******' CREATEUSER
-- permissão de criar usuários
ALTER ROLE nomeuser VALID UNTIL '12/05/2006';
ALTER ROLE fred VALID UNTIL ’infinity’;
ALTER ROLE miriam CREATEROLE CREATEDB; -- poderes para criar bancos

Obs.: Lembrando que ALTER ROLE é uma extensão do PostgreSQL.

Listando todos os usuários:
SELECT usename FROM pg_user;
A tabela pg_user é uma tabela de sistema (_pg) que guarda todos os usuários do PostgreSQL.

Também podemos utilizar:
\du ou \dg

Criando Um Grupo de usuários
CREATE GROUP nomedogrupo;

Adicionar/Remover Usuários de um Grupo
ALTER GROUP nomegrupo ADD USER user1, user2,user3 ;
ALTER GROUP nomegrupo DROP USER user1, user2 ;

Excluindo Grupo
DROP GROUP nomegrupo;

Obs.: isso remove somente o grupo, não remove os usuários.

Listando todos os grupos:
SELECT groname FROM pg_group;

Privilégios
Dando Privilégios A Um Usuário
GRANT UPDATE ON nometabela TO nomeusuario;

Dando Privilégios A Um Grupo Inteiro
GRANT SELECT ON nometabela TO nomegrupo;

Removendo Todos os Privilégios de Todos os Users
REVOKE ALL ON nometabela FROM PUBLIC

Privilégios
O superusuário tem direito a fazer o que bem entender em qualquer banco de dados do SGBD.
O usuário que cria um objeto (banco, tabela, view, etc) é o dono do objeto.
Para que outro usuário tenha acesso ao mesmo deve receber privilégios.
Existem vários privilégios diferentes: SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE e USAGE.
Os privilégios aplicáveis a um determinado tipo de objeto variam de acordo com o tipo do objeto (tabela, função, etc.).

O comando para conceder privilégios é o GRANT. O de remover é o REVOKE.

GRANT UPDATE ON contas TO joel;
Dá a joel o privilégio de executar consultas update no objeto contas.

GRANT SELECT ON contas TO GROUP contabilidade;

REVOKE ALL ON contas FROM PUBLIC;

Os privilégios especiais do dono da tabela (ou seja, os direitos de DROP, GRANT, REVOKE, etc.) são sempre inerentes à condição de ser o dono, não podendo ser concedidos ou revogados. Porém, o dono do objeto pode decidir revogar seus próprios privilégios comuns como, por exemplo, tornar a tabela somente para leitura para o próprio, assim como para os outros.

Normalmente, só o dono do objeto (ou um superusuário) pode conceder ou revogar privilégios para um objeto.

-- Criação dos grupos
CREATE GROUP adm;
CREATE USER paulo ENCRYPTED PASSWORD 'paulo' CREATEDB CREATEUSER;

-- Criação dos Usuários do Grupo adm
CREATE USER andre ENCRYPTED PASSWORD 'andre' CREATEDB IN GROUP adm;
CREATE USER michela ENCRYPTED PASSWORD 'michela' CREATEDB IN GROUP adm;

O usuário de sistema (super usuário) deve ser um usuário criado exclusivamente para o PostgreSQL. Nunca devemos torná-lo dono de nenhum executável.

Os nomes de usuários são globais para todo o agrupamento de bancos de dados, ou seja, podemos utilizar um usuário com qualquer dos bancos.

Os privilégios DROP, GRANT, REVOKE, etc pertencem ao dono do objeto não podendo ser concedidos ou revogados. O máximo que um dono pode fazer é abdicar de seus privilégios e com isso ninguém mais teria os mesmos e o objeto seria somente leitura para todos.
Exemplo: para permitir a um usuário apenas os privilégios de INSERT, UPDATE e SELECT e não permitir o de DELETE em uma tabela, use:

REVOKE ALL ON tabela FROM usuario;
GRANT SELECT,UPDATE,INSERT ON tabela TO usuario;