Dicas de PostgreSQL

Criando banco

**************************************************************************

torne-se root

gutocarvalho@pgsql01:~$ sudo -i

torne-se postgresql

root@pgsql:~$# su postgres

acesse o CLI do postgres

postgres@pgsql01:~$ psql

agora estamos na CLI

postgres=#

Criando usuario com senha

postgres=# CREATE USER fernandao WITH PASSWORD 'senha';

Criando o banco

postgres=# CREATE DATABASE eleicoes_carga;

Dando permissoes ao usuario para acessar o banco

postgres=# GRANT ALL PRIVILEGES ON DATABASE projeto_carga to fernando;

alterando owner do banco

postgres=# ALTER DATABASE projeto_carga OWNER TO fernando;

liberando acesso de uma maquina ao banco

após isto basta liberar o ip o cara no pg_hba.conf, veja um exemplo

# IPv4 local connections:  

host    all         all         127.0.0.1/32          md5  
host    all         all         172.16.10.20/32       md5 # gandalf  

configurando postgres para listar em todas as interfaces

e tenha certeza que o postgresql está rodando em todas as interfaces, veja isso em postgresql.conf

listen_addresses = '0.0.0.0'            # what IP address(es) to listen on;

precisa reiniciar o banco.

dump

dump de um banco inteiro - formato sql puro

pg_dump -U usuario -h ip_servidor nomedobanco > nomedobanco.sql

dump de um banco inteiro - formato binario

pg_dump -U usuario -h ip_servidor -Fc -f banco.dump nome_do_banco

dump de uma tabela do banco

pg_dump -U usuario -h ip_servidor -Fc -t tabela -f tabela.pgdump nome_do_banco

dump de um table de um schema

pg_dump -U postgres -h 127.0.0.1 -Fc -n schema -t tabela -f tabela.pgdump banco

restore

para restaurar um banco com dump em formato SQL

psql -U usuario -h ip_do_servidor -f nomeodabanco.sql nomedobanco

para restaurar um banco com dump em formato binário

pg_restore -U usuario -h ip_servidor -d nome_do_banco arquivo.dump

para restaurar somente os dados:

pg_restore -U usuario -h ip_servidor -a -d nome_do_banco arquivo.dump

para restaurar somente um determinado esquema

pg_restore -U usuario -h ip_servidor -n meu_esquema -d nome_do_banco arquivo.dump

para restaurar apenas os dados de uma tabela especifica

pg_restore -U usuario -h ip_servidor -a -t tabela_especifica -d nome_do_banco arquivo.dump

exemplos

fazendo dump de tabela da radioagencia

pg_dump -Fc -U drupal -h 127.0.0.1 -t drupal_webform -f arquivo -d drupal drupal_webform

restaurando tabela da radioagencia

pg_restore -Fc -U drupal -h 127.0.0.1 -t drupal_webform -d drupal drupal_webform

Observe o formato do DUMP, ele precisa ser tipo C ou tipo F para que o pg_restore consiga recuperar, se for plain text vai dar erro.

dicas soltas

alterando senha do usuario

postgres=#alter user fernando with password 'senha';

tamanho de uma base

dados sem arrendodamento, tamanho do bancos apenas

postgres=# SELECT pg_database_size('geekdb');

arredondando, tamanho do banco apenas

postgres=# SELECT pg_size_pretty(pg_database_size('geekdb'));

tamanho real do banco no disco arredondando

postgres=# SELECT pg_size_pretty(pg_database_size('somedatabase')) As fulldbsize;

tamanho de uma tabela

postgres=# SELECT pg_size_pretty(pg_total_relation_size('drupal_cache'));

nde

tamanho de todas as tabelas

postgres=# SELECT
  relname as "Table",
  pg_size_pretty(pg_total_relation_size(relid)) As "Size",
  pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
  FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
  

tamanho de todos as tabelas e objetos maiores que 64k

postgres=# SELECT
  relname AS objectname,
  relkind AS objecttype,
  reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
  FROM pg_class
  WHERE relpages >= 8
  ORDER BY relpages DESC;
  
  

tamanho da maior tabela para menor

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

psql

usando o console

listando bancos

postgres=# \l

conectando a um banco

postgres=# \c nome_do_banco

outros

work mem

Quando algum processo pesado é executado, por exemplo a geracao de indices do drupal ou consultas que usam muito ORDER BY, um diretorio pgsqltemp será criado caso o uso da memoria setado na variavel WORKMEM seja execedido.

Nestes caso o aconselhável é aumentar - perante monitoramento - o parametro WORK_MEM na sessão em que está executando a query ou rodando os indices.

SET WORK_MEM TO "64MB"

Esse valor é por consulta, se colocar um valor muito alto a máquina vai sentar bonito, procure um valor entre 64 e 128, monitorando o servidor para verificar o impacto.

Alterar diretório de dados PostgreSQL

Código:

parar o serviço do postgresql: service postgresql-9.3 stop

Edite o script start-up

Código:

root @ host # vi /etc/init.d/postgresql

Altere a variável PGDATA e PGLOG para onde quiser (mudanças em vermelho ) ...

Código:

# Definir padrões para as variáveis ​​de configuração
PGENGINE=/usr/bin
PGPORT=5432
PGDATA=/path/to/pgdata
if [ -f "$PGDATA/PG_VERSION" ] && [ -d "$PGDATA/base/template1" ]
then
        echo "Usando estrutura de diretórios de estilo antigo"
else
        PGDATA=/d2/pgdata
fi
PGLOG=/path/to/pgdata/pgstartup.log

# Override defaults from /etc/sysconfig/pgsql if file is present 
[ -f /etc/sysconfig/pgsql/${NAME} ] && . /etc/sysconfig/pgsql/${NAME}

export PGDATA
export PGPORT

Crie o diretório ... e definir as permissões

Código:

root @ host # mkdir-p /path/ to /pgdata
root @ host # chown postgres: postgres /path/to/pgdata

Inicializar o banco de dados ...

Código:

root @ host # su - postgres-c "initdb-D /path/ to /pgdata"

Agora você pode começar postgres

Código:

root @ host # service postgresql-9.3 start

Agora você pode verificar isso com o ps e você verá (em vermelho ) que a dir pgdata é onde o banco de dados é

Código:

root @ host # ps aux | grep pós
 postgres 11140 0,0 0,1 122556 3372? S 10:41 0:00 / usr / bin / postmaster-p 5432-D / path / to / pgdata
postgres 11142 0,0 0,0 111736 672? S 10:41 0:00 postgres: processo logger                 
postgres 11144 0,0 0,0 122692 1168? S 10:41 0:00 postgres: processo escritor                 
postgres 11145 0,0 0,0 112736 672? S 10:41 0:00 postgres: Estatísticas processo de buffer           
postgres 11146 0,0 0,0 111864 848? S 10:41 0:00 postgres: Status de processo de coletor        
raiz 11186 0,0 0,0 61120 724 pts / 1 S + 10:58 0:00 grep pós

Instalação PostgreSQL 9.3

Existe diversa maneira de fazer a instalação pelo fonte compilando e instalando, baixando o pacote .rpm e usando gerenciador de pacote yum que vai fazer todo trabalho automático. Qual escolher vai depender do cenário para este vamos usar o yum  instalando a versão 9.3

Primeiro vamos atualizar o S.O

#  yum update 

Vamos inserir no repositório o caminho para o pacote postgresql 9.3

# rpm -i http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-redhat93-9.3-1.noarch.rpm

Agora conseguimos instalar através do yum install

# yum install postgresql93-server postgresql93-contrib  

Agora que já instalamos vamos iniciar o banco depois o serviço.

# service postgresql-9.3 initdb 
# service postgresql-9.3 start
# chkconfig postgresql-9.3 on

Precisamos ajustar o servidor para permitir conexões externas na porta "5432"

Primeiro vamos liberar no iptables, editar o arquivo "/etc/sysconfig/iptables"

# vim /etc/sysconfig/iptables

Na linha 5 coloque a regra abaixo:

-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

Apos salvar o arquivo reinicie o serviço do iptables

# service iptables restart

Agora precisa dizer para o Postgre aceitar conexões externa, vamos editar "/var/lib/pgsql/9.3/data/postgresql.conf"

# vim /var/lib/pgsql/9.3/data/postgresql.conf

precisamos tirar o comentário de duas linhas "listen_addresses" e "port" abaixo exemplo como deve ficar:

próximo passo e editar o arquivo "/var/lib/pgsql/9.3/data/pg_hba.conf" na coluna METHOD trocar ident por md5 e inserir faixa de rede ou ip que vai ter acesso ao servidor, abaixo exemplo como deve ficar:

reiniciar o serviço para as configurações aplicar

#  service postgresql-9.3 restart

Vamos definir senha do usuario postgres

# su postgres

bash-4.1$  psql postgres

postgres=# alter user postgres password 'sua_senha';

Caso queira acessar o Postgre de uma maquina Windows com interface grafica e só instalar o programa pgAdmin link para download http://www.postgresql.org/ftp/pgadmin3/release/v1.18.1/win32/ depois de instalar vai encontrar essa tela e só colocar os parâmetros de conexão.

Pronto Concluído.