Base de Conhecimento

Introdução

Os sistemas relacionais de gerenciamento de banco de dados são um componente essencial de muitos sites e aplicativos. Eles fornecem uma maneira estruturada de armazenar, organizar e acessar informações.

O PostgreSQL , ou Postgres, é um sistema de gerenciamento de banco de dados relacional que fornece uma implementação da linguagem de consulta SQL. É uma escolha popular para muitos projetos pequenos e grandes e tem a vantagem de ser compatível com os padrões e ter muitos recursos avançados, como transações confiáveis e simultaneidade sem bloqueios de leitura .

Neste guia, você instalará o Postgres em um servidor CentOS 7 e examinará algumas maneiras básicas de usá-lo.

Pré-requisitos

Para acompanhar este tutorial, você precisará de:

• Um servidor CentOS 7 que foi configurado seguindo nosso guia Configuração inicial do servidor com o CentOS 7 e nosso tutorial Etapas adicionais adicionais para novos servidores CentOS 7 , incluindo um usuário não raiz com privilégios de sudo e um firewall configurado firewalld.
  • Para configurar firewalld, siga a seção Configurando um firewall básico do tutorial Etapas adicionais adicionais para novos servidores CentOS 7 .
  • Os bancos de dados podem ser particularmente vulneráveis ​​a alterações de horário do sistema se estiverem muito ativos e tiverem registros de data e hora nos registros internos. Para evitar alguns comportamentos inconsistentes que podem surgir a partir de relógios fora de sincronia, certifique-se de configurar o Network Time Protocol (NTP) sincronização seguindo o Configure Time Synchronization Protocol Timezones ea Rede secção dos passos adicionais recomendado para novos CentOS 7 Servidores tutorial .

Etapa 1 - Instalando o PostgreSQL

O Postgres pode ser instalado usando repositórios CentOS padrão. Porém, no momento da redação deste tutorial, a versão disponível no repositório do CentOS 7 Base está obsoleta. Portanto, este tutorial usará o repositório oficial do Postgres.

Antes de prosseguir com a configuração de um novo repositório, exclua a pesquisa de postgresqlpacotes do repositório do CentOS-Base. Caso contrário, as dependências podem resolver para o postgresqlfornecido pelo repositório base.

Abra o arquivo de configuração do repositório usando seu editor de texto preferido. Este tutorial usará vim:

• sudo vi /etc/yum.repos.d/CentOS-Base.repo

Encontre as seções [base]e [updates], entre no modo de inserção pressionando ie insira a exclude=postgresql*linha nas duas seções. Como resultado, seu arquivo será semelhante ao seguinte, com novas linhas destacadas:

...
[base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os&infra=$infra
#baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7

exclude=postgresql*

#released updates
[updates]
name=CentOS-$releasever - Updates
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates&infra=$infra
#baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
exclude=postgresql*
...

Quando terminar, pressione ESCpara sair modo de inserção, em seguida, :wqe ENTERpara salvar e sair do arquivo. Para saber mais sobre o editor de texto vi e seu sucessor vim, consulte nosso tutorial Instalando e usando o editor de texto Vim em um servidor Cloud .

Agora, instale um pacote de configuração de repositório usando o repositório oficial do PostgreSQL para o CentOS:

• sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Quando solicitado, confirme a instalação com y.

O repositório do PostgreSQL inclui informações para todas as versões disponíveis do PostgreSQL. Você pode ver todos os pacotes e versões disponíveis usando o seguinte comando:

• yum list postgresql*

Escolha e instale a versão desejada do PostgreSQL. Neste guia, você usará a versão PostgreSQL 11.

Para instalar o servidor PostgreSQL, use o seguinte comando:

• sudo yum install postgresql11-server

Durante o processo de instalação, você será perguntado sobre a importação de uma chave GPG com um prompt como o seguinte:

...
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <[email protected]>"
 Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
 Package    : pgdg-redhat-repo-42.0-5.noarch (installed)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]:

Confirme com ypara que a instalação possa ser concluída.

Agora que o software está instalado, você executará algumas etapas de inicialização para preparar um novo cluster de banco de dados para o PostgreSQL.

Etapa 2 - Criando um novo cluster de banco de dados PostgreSQL

Você precisa criar um novo cluster de banco de dados PostgreSQL antes de poder usar seu banco de dados Postgres. Um cluster de banco de dados é uma coleção de bancos de dados gerenciados por uma única instância do servidor. Criar um cluster de banco de dados consiste em criar os diretórios nos quais serão colocados os dados do banco de dados, gerando as tabelas de catálogo compartilhados, e criar o template1e postgresbancos de dados.

O template1banco de dados é necessário para criar um novo banco de dados. Tudo o que é armazenado nele será colocado em um novo banco de dados quando ele for criado. Um postgresbanco de dados é um banco de dados padrão projetado para uso por usuários, utilitários e aplicativos de terceiros.

Crie um novo cluster de banco de dados PostgreSQL com initdb:

• sudo /usr/pgsql-11/bin/postgresql-11-setup initdb

Você verá a seguinte saída:

Initializing database ... OK

Agora inicie e ative o PostgreSQL usando systemctl:

• sudo systemctl start postgresql-11
• sudo systemctl enable postgresql-11

Isso dará a seguinte saída

Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-11.service to /usr/lib/systemd/system/postgresql-11.service.

Agora que o PostgreSQL está em funcionamento, você passará a usar funções para aprender como o Postgres funciona e como é diferente dos sistemas de gerenciamento de banco de dados semelhantes que você pode ter usado no passado.

Etapa 3 - Usando funções e bancos de dados PostgreSQL

Por padrão, o Postgres usa um conceito chamado de funções para lidar com autenticação e autorização. De certa forma, elas são semelhantes às contas comuns no estilo Unix, mas o Postgres não distingue entre usuários e grupos e prefere o papel de termo mais flexível.

Após a instalação, o Postgres é configurado para usar a autenticação de identificação , o que significa que associa as funções do Postgres a uma conta de sistema Unix / Linux correspondente. Se existir uma função no Postgres, um nome de usuário Unix / Linux com o mesmo nome poderá entrar como essa função.

O procedimento de instalação criou uma conta de usuário chamada postgres que está associada à função padrão do Postgres. Para usar o Postgres, você pode fazer login nessa conta.

Existem algumas maneiras de usar essa conta para acessar o Postgres.

Mudando para a conta do postgres

Mude para a conta do postgres no seu servidor digitando:

• sudo -i -u postgres

Agora você pode acessar um prompt do Postgres imediatamente, digitando:

• psql

Isso fará o logon no prompt do PostgreSQL e, a partir daqui, você poderá interagir com o sistema de gerenciamento de banco de dados imediatamente.

Saia do prompt do PostgreSQL digitando:

• \q

Isso o levará de volta ao prompt de comando do Linux do postgres . Agora retorne à sua conta sudo original com o seguinte:

• exit

Acessando um prompt do Postgres sem alternar contas

Você também pode executar o comando que deseja com a conta do postgres diretamente sudo.

Por exemplo, no último exemplo, você foi instruído a acessar o prompt do Postgres alternando primeiro para o usuário do postgres e depois executando psqlpara abrir o prompt do Postgres. Você pode fazer isso em uma etapa executando o comando único psqlcomo o usuário do postgressudo , assim:

• sudo -u postgres psql

Isso fará o logon direto no Postgres sem o bashshell intermediário .

Novamente, você pode sair da sessão interativa do Postgres digitando:

• \q

Nesta etapa, você usou a conta do postgres para acessar o psqlprompt. Mas muitos casos de uso exigem mais de uma função do Postgres. Leia para aprender como configurar novas funções.

Etapa 4 - Criando uma nova função

Atualmente, você apenas tem a função postgres configurada no banco de dados. Você pode criar novas funções na linha de comando com o createrolecomando O --interactivesinalizador solicitará o nome da nova função e também perguntará se ela deve ter permissões de superusuário.

Se você está logado como a conta do postgres , pode criar um novo usuário digitando:

• createuser --interactive

Se, em vez disso, você preferir usar sudopara cada comando sem alternar da sua conta normal, digite:

• sudo -u postgres createuser --interactive

O script solicitará algumas opções e, com base em suas respostas, executará os comandos corretos do Postgres para criar um usuário com suas especificações. Para este tutorial, crie um usuário sammy e dê a ele privilégios de superusuário:

Enter name of role to add: sammy
Shall the new role be a superuser? (y/n) y

Você pode obter mais controle passando algumas sinalizações adicionais. Confira as opções consultando a manpágina:

• man createuser

Sua instalação do Postgres agora tem um novo usuário, mas você ainda não adicionou nenhum banco de dados. A próxima seção descreve esse processo.

Etapa 5 - Criando um novo banco de dados

Outra suposição que o sistema de autenticação do Postgres faz por padrão é que, para qualquer função usada para efetuar login, essa função terá um banco de dados com o mesmo nome que ele pode acessar.

Isso significa que, se o usuário que você criou na última seção for chamado sammy , essa função tentará se conectar a um banco de dados que também é chamado sammypor padrão. Você pode criar o banco de dados apropriado com o createdbcomando

Se você estiver logado como a conta do postgres , digite algo como:

• createdb sammy

Se, em vez disso, você preferir usar sudopara cada comando sem alternar da sua conta normal, digite:

• sudo -u postgres createdb sammy

Essa flexibilidade fornece vários caminhos para a criação de bancos de dados, conforme necessário.

Agora que você criou um novo banco de dados, efetue login com sua nova função.

Etapa 6 - Abrindo um prompt do Postgres com a nova função

Para identefetuar login com autenticação baseada, você precisará de um usuário Linux com o mesmo nome que sua função e banco de dados do Postgres.

Se você não tiver um usuário Linux correspondente disponível, poderá criar um com o addusercomando Você precisará fazer isso na sua conta não raiz com sudoprivilégios (ou seja, não está logado como usuário do postgres ):

• sudo adduser sammy

Quando essa nova conta estiver disponível, você poderá alternar e conectar-se ao banco de dados digitando:

• sudo -i -u sammy
• psql

Ou, você pode fazer isso em linha:

• sudo -u sammy psql

Este comando fará o login automaticamente.

Se você deseja que seu usuário se conecte a um banco de dados diferente, pode fazê-lo especificando o banco de dados da seguinte forma:

• psql -d postgres

Uma vez logado, você pode verificar suas informações de conexão atuais digitando:

• \conninfo

Isso mostrará a seguinte saída:

You are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".

Isso é útil se você estiver se conectando a bancos de dados não padrão ou com usuários não padrão.

Após conectar-se ao seu banco de dados, agora você pode tentar criar e excluir tabelas.

Etapa 7 - Criando e excluindo tabelas

Agora que você sabe como se conectar ao sistema de banco de dados PostgreSQL, pode aprender algumas tarefas básicas de gerenciamento do Postgres.

Primeiro, crie uma tabela para armazenar alguns dados. Como exemplo, você fará uma tabela que descreve alguns equipamentos de playground.

A sintaxe básica para este comando é a seguinte:

CREATE TABLE table_name (
    column_name1 col_type (field_length) column_constraints,
    column_name2 col_type (field_length),
    column_name3 col_type (field_length)
);

Esses comandos dão um nome à tabela e, em seguida, definem as colunas, o tipo da coluna e o comprimento máximo dos dados do campo. Opcionalmente, você também pode adicionar restrições de tabela para cada coluna.

Você pode aprender mais no tutorial Como criar, remover e gerenciar tabelas no PostgreSQL em um servidor Cloud .

Para fins de demonstração, crie uma tabela simples como esta:

CREATE TABLE playground (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
    install_date date
);

Esses comandos criarão uma tabela que inventaria equipamentos de playground. Isso começa com um ID de equipamento, que é do serialtipo. Esse tipo de dados é um número inteiro com incremento automático. Você também atribuiu a esta coluna a restrição primary key, o que significa que os valores devem ser únicos e não nulos.

Para duas das colunas ( equip_ide install_date), os comandos não especificam um comprimento de campo. Isso ocorre porque alguns tipos de coluna não exigem um comprimento definido, porque o comprimento é implícito pelo tipo.

Os próximos dois comandos criam colunas para o equipamento typee color, respectivamente, cada um dos quais não pode estar vazio. O comando após isso cria uma locationcoluna e uma restrição que requer que o valor seja um dos oito valores possíveis. O último comando cria uma coluna de data que registra a data em que você instalou o equipamento.

Você pode ver sua nova tabela digitando:

• \d

Isso mostrará a seguinte saída:

                  List of relations
 Schema |          Name           |   Type   | Owner 
--------+-------------------------+----------+-------
 public | playground              | table    | sammy
 public | playground_equip_id_seq | sequence | sammy
(2 rows)

Sua mesa de playground está aqui, mas também há algo chamado playground_equip_id_seqque é do tipo sequence. Esta é uma representação do serialtipo que você deu à sua equip_idcoluna. Isso controla o próximo número na sequência e é criado automaticamente para colunas desse tipo.

Se você quiser ver apenas a tabela sem a sequência, digite:

• \dt

Isso produzirá o seguinte:

          List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 public | playground | table | sammy
(1 row)

Nesta etapa, você criou uma tabela de amostra. Na próxima etapa, você tentará adicionar, consultar e excluir entradas nessa tabela.

Etapa 8 - Adicionando, consultando e excluindo dados em uma tabela

Agora que você possui uma tabela, pode inserir alguns dados nela.

Como exemplo, adicione um slide e um balanço chamando a tabela à qual você deseja adicionar, nomeando as colunas e fornecendo dados para cada coluna, assim:

• INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
• INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');

Você deve tomar cuidado ao inserir os dados para evitar alguns problemas comuns. Por um lado, não coloque os nomes das colunas entre aspas, mas os valores das colunas inseridos precisam de aspas.

Outra coisa a ter em mente é que você não insere um valor para a equip_idcoluna. Isso ocorre porque isso é gerado automaticamente sempre que uma nova linha na tabela é criada.

Recupere as informações que você adicionou, digitando:

• SELECT * FROM playground;

Você verá a seguinte saída:

 equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
        1 | slide | blue   | south     | 2017-04-28
        2 | swing | yellow | northwest | 2018-08-16
(2 rows)

Aqui, você pode ver que o seu equip_idfoi preenchido com sucesso e que todos os seus outros dados foram organizados corretamente.

Se o slide no playground quebrar e você precisar removê-lo, também poderá remover a linha da sua mesa digitando:

• DELETE FROM playground WHERE type = 'slide';

Consulte a tabela novamente:

• SELECT * FROM playground;

Você verá o seguinte:

 equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
        2 | swing | yellow | northwest | 2018-08-16
(1 row)

Observe que seu slide não faz mais parte da tabela.

Agora que você adicionou e excluiu entradas em sua tabela, tente adicionar e excluir colunas.

Etapa 9 - Adicionando e excluindo colunas de uma tabela

Após criar uma tabela, você pode modificá-la para adicionar ou remover colunas. Adicione uma coluna para mostrar a última visita de manutenção para cada equipamento digitando:

• ALTER TABLE playground ADD last_maint date;

Se você visualizar as informações da tabela novamente, verá que a nova coluna foi adicionada (mas nenhum dado foi inserido):

• SELECT * FROM playground;

Você verá o seguinte:

 equip_id | type  | color  | location  | install_date | last_maint 
----------+-------+--------+-----------+--------------+------------
        2 | swing | yellow | northwest | 2018-08-16   | 
(1 row)

Excluir uma coluna é igualmente simples. Se você achar que sua equipe de trabalho usa uma ferramenta separada para acompanhar o histórico de manutenção, exclua a coluna digitando:

• ALTER TABLE playground DROP last_maint;

Isso exclui a last_maintcoluna e quaisquer valores encontrados nela, mas deixa todos os outros dados intactos.

Depois de adicionar e excluir colunas, você pode tentar atualizar os dados existentes na etapa final.

Etapa 10 - Atualizando dados em uma tabela

Até agora, você aprendeu como adicionar registros a uma tabela e como excluí-los, mas este tutorial ainda não abordou como modificar as entradas existentes.

Você pode atualizar os valores de uma entrada existente consultando o registro que deseja e configurando a coluna para o valor que deseja usar. Você pode consultar o swingregistro (isso corresponderá a todos os giros da sua tabela) e altere sua cor para red:

• UPDATE playground SET color = 'red' WHERE type = 'swing';

Você pode verificar se a operação foi bem-sucedida consultando os dados novamente:

• SELECT * FROM playground;

Você verá o seguinte:

 equip_id | type  | color | location  | install_date 
----------+-------+-------+-----------+--------------
        2 | swing | red   | northwest | 2010-08-16
(1 row)

Como você pode ver, seu slide agora está registrado como vermelho.

Conclusão

Agora você está configurado com o PostgreSQL no seu servidor CentOS 7. No entanto, ainda há muito mais a aprender com o Postgres. Aqui estão mais alguns guias que abordam como usar o Postgres: