====== Instalação por código fonte - Debian ====== ===== Vagranfile ===== $ cat Vagrantfile # -*- mode: ruby -*- # vi: set ft=ruby : ENV['VAGRANT_DEFAULT_PROVIDER'] = 'libvirt' vms = { "pgsql-director" => {"memory" => "4096", "cpu" => "2", "ip" => "250", "box" => "g3anmart1ns/debian11" }, "pgsql-client" => {"memory" => "1024", "cpu" => "1", "ip" => "10", "box" => "g3anmart1ns/debian11" } } Vagrant.configure("2") do |config| config.vm.box_check_update = false vms.each do |name, conf| config.vm.define "#{name}" do |vms| vms.vm.network :private_network, :ip => "10.200.250.#{conf["ip"]}" vms.vm.box = "#{conf["box"]}" vms.vm.hostname = "#{name}" vms.vm.provider :libvirt do |lv| lv.memory = conf["memory"] lv.cpus = conf["cpu"] lv.cpu_mode = "host-passthrough" lv.cputopology :sockets => "1", :cores => conf["cpu"], :threads => "1" lv.disk_driver :cache => "none" if name == 'pgsql-director' lv.storage :file, :size => '32G' end end end end config.vm.synced_folder ".", "/vagrant", disabled: true end ===== Arquitetura de diretórios sugerida ===== ^Tipo^Localização^ |Instalação|/usr/local/pgsql/| |Configuração|/var/local/pgsql//data| |Dados (PGDATA)|/var/local/pgsql//data| |Binários|/usr/local/pgsql//bin| ===== Preparando partição para a instalação do PodtgreSQL ===== vagrant@pgsql-director:~$ sudo su - root@pgsql-director:~# fdisk -l Disco /dev/vda: 32 GiB, 34359738368 bytes, 67108864 setores Unidades: setor de 1 * 512 = 512 bytes Tamanho de setor (lógico/físico): 512 bytes / 512 bytes Tamanho E/S (mínimo/ótimo): 512 bytes / 512 bytes Tipo de rótulo do disco: dos Identificador do disco: 0x5c4d2e13 Dispositivo Inicializar Início Fim Setores Tamanho Id Tipo /dev/vda1 * 2048 65107967 65105920 31G 83 Linux /dev/vda2 65110014 67106815 1996802 975M 5 Estendida /dev/vda5 65110016 67106815 1996800 975M 82 Linux swap / Solaris Disco /dev/vdb: 32 GiB, 34359738368 bytes, 67108864 setores Unidades: setor de 1 * 512 = 512 bytes Tamanho de setor (lógico/físico): 512 bytes / 512 bytes Tamanho E/S (mínimo/ótimo): 512 bytes / 512 bytes root@pgsql-director:~# apt install lvm2 root@pgsql-director:~# pvcreate /dev/vdb root@pgsql-director:~# vgcreate vgPGSQL /dev/vdb root@pgsql-director:~# lvcreate -l 85%FREE -n lvPGSQL vgPGSQL root@pgsql-director:~# lvdisplay --- Logical volume --- LV Path /dev/vgPGSQL/lvPGSQL LV Name lvPGSQL VG Name vgPGSQL LV UUID nCKWWy-9O4E-pwvk-VwdT-w9uK-xTd8-Eo8j7K LV Write Access read/write LV Creation host, time pgsql-director, 2022-12-08 17:03:44 -0300 LV Status available # open 0 LV Size <27,20 GiB Current LE 6962 Segments 1 Allocation inherit Read ahead sectors auto - currently set to 256 Block device 253:0 root@pgsql-director:~# mkfs.xfs /dev/mapper/vgPGSQL-lvPGSQL mke2fs 1.46.2 (28-Feb-2021) Discarding device blocks: done Creating filesystem with 7129088 4k blocks and 1782368 inodes Filesystem UUID: 66f3cbfc-5ea2-4058-929d-476b8aee88fd Superblock backups stored on blocks: 32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 4096000 Allocating group tables: done Writing inode tables: done Creating journal (32768 blocks): done Writing superblocks and filesystem accounting information: done root@pgsql-director:~# mkdir /var/local/pgsql root@pgsql-director:~# UUID=`sudo blkid -o export /dev/mapper/vgPGSQL-lvPGSQL | grep UUID` root@pgsql-director:~# cp -p /etc/fstab{,.dist} root@pgsql-director:~# echo "${UUID} /var/local/pgsql xfs defaults 1 2" | tee -a /etc/fstab UUID=66f3cbfc-5ea2-4058-929d-476b8aee88fd /var/local/pgsql ext4 defaults 1 2 root@pgsql-director:~# mount -a root@pgsql-director:~# df -h Sist. Arq. Tam. Usado Disp. Uso% Montado em udev 2,0G 0 2,0G 0% /dev tmpfs 394M 428K 393M 1% /run /dev/vda1 31G 1,4G 28G 5% / tmpfs 2,0G 0 2,0G 0% /dev/shm tmpfs 5,0M 0 5,0M 0% /run/lock tmpfs 394M 0 394M 0% /run/user/1000 /dev/mapper/vgPGSQL-lvPGSQL 27G 24K 26G 1% /var/local/pgsql ===== Vatiáveis de ambiente de pacotes ===== # Pacotes a serem removidos após a instalação PKG_RM='bison gcc flex make cmake' # Pacotes a serem mantidos: # Pacotes comuns a serem mantidos: PKG='bzip2 wget git' # Variável de ambiente para pacotes Debian: PKG_DEB="libreadline-dev libssl-dev libxml2-dev libldap2-dev \ uuid-dev python3-dev" ==== Instalação de pacotes e limpeza dos pacotes baixados ==== apt update && apt install -y ${PKG} ${PKG_RM} ${PKG_DEB} && apt clean ==== Habilitar configurações de localidades (locales) pt_BR.utf8 e en_US.utf8 ==== sed -i -e 's/# en_US.UTF-8 UTF-8/en_US.UTF-8 UTF-8/' /etc/locale.gen sed -i -e 's/# pt_BR.UTF-8 UTF-8/pt_BR.UTF-8 UTF-8/' /etc/locale.gen locale-gen ===== Definição de variáveis gerais ===== # Variável de ambiente para versão completa do Postgres via prompt: read -p \ 'Digite o número de versão completo (X.Y) do PostgreSQL a ser baixado: ' \ PGVERSION # Definição da variável de ambiente para a versão majoritária: export PGMAJOR=`echo ${PGVERSION} | cut -f1 -d.` # Diretório de instalação do PostgreSQL PGHOME="/usr/local/pgsql/${PGMAJOR}" # Diretório de binários PGBIN="${PGHOME}/bin" # Diretório home do usuário postgres PGUSERHOME="/var/local/pgsql" # Diretório de logs PGLOG="/var/log/pgsql/${PGMAJOR}" # Diretório de dados do PostgreSQL PGDATA="${PGUSERHOME}/${PGMAJOR}/data" # Diretório de logs de transação PGWAL="${PGUSERHOME}/${PGMAJOR}/wal" # Diretório de estatísticas temporárias PG_STAT_TEMP="${PGUSERHOME}/${PGMAJOR}/pg_stat_tmp" # Diretório de headers C PGINCLUDEDIR="/usr/local/include/pgsql/${PGMAJOR}" [[https://www.postgresql.org/download|Encontre aqui a versão desejada]] ===== Variáveis de ambiente para o processo de compilação ===== # Variável de ambiente do executável Python 3 export PYTHON=`which python3` # Opções do configure CONFIGURE_OPTS=" --prefix=${PGHOME} \ --with-python \ --with-libxml \ --with-openssl \ --with-ldap \ --with-uuid=e2fs \ --includedir=${PGINCLUDEDIR}" # Protege o processo principal do OOM Killer CPPFLAGS="-DLINUX_OOM_SCORE_ADJ=0" # Número de jobs conforme a quantidade de cores da CPU (cores + 1): NJOBS=`expr \`nproc\` + 1` # Opções do make MAKEOPTS="-j${NJOBS}" # Tipo de hardware CHOST="x86_64-unknown-linux-gnu" # Flags de otimização para o make CFLAGS="-march=native -O2 -pipe" CXXFLAGS="$CFLAGS" ==== Criação de um grupo e usuário de sistema ==== groupadd -r postgres &> /dev/null useradd \ -c 'PostgreSQL system user' \ -s /bin/bash \ -k /etc/skel \ -d ${PGUSERHOME} \ -g postgres \ -m -r postgres &> /dev/null ==== Criar o arquivo .pgvars com seu respectivo conteúdo no diretório do usuário home postgres ==== cat << EOF > ~postgres/.pgvars # Environment Variables ====================================================== # PostgreSQL major version PGMAJOR="${PGMAJOR}" # Postgre=SQL installation directory PGHOME="/usr/local/pgsql/\${PGMAJOR}" # Binary directory PGBIN="${PGHOME}/bin" # Library directories export LD_LIBRARY_PATH="\${PGHOME}/lib:\${LD_LIBRARY_PATH}" # Manuals directories export MANPATH="\${PGHOME}/man:\${MANPATH}" # Path to binaries export PATH="\${PGBIN}:\${PATH}" # PostgreSQL data directory export PGDATA="${PGUSERHOME}/\${PGMAJOR}/data" # Unset variables unset PGMAJOR PGHOME PGBIN EOF ==== Adiciona linha no arquivo de perfil do usuário postgres para ler o arquivo ~/.pgvars e aplicá-las ==== if [ -f ~postgres/.bash_profile ]; then echo -e "\nsource ~/.pgvars" >> ~postgres/.bash_profile else echo -e "\nsource ~/.pgvars" >> ~postgres/.profile fi ==== ~postgres/.psqlrc otimizado ==== cat << EOF > ~postgres/.psqlrc \set HISTCONTROL ignoreboth \set COMP_KEYWORD_CASE upper \x auto EOF ===== Criação de diretórios ===== mkdir -pm 0700 ${PGLOG} ${PGDATA} ${PGWAL} ${PG_STAT_TEMP} ==== Download do código-fonte ==== wget -c \ https://ftp.postgresql.org/pub/source/v${PGVERSION}/postgresql-\ ${PGVERSION}.tar.bz2 -P /tmp/ === Ir para /tmp onde o arquivo foi baixado, descompactá-lo === cd /tmp/ && tar xf postgresql-${PGVERSION}.tar.bz2 === Após a descompactação acessar a pasta do código-fonte === cd postgresql-${PGVERSION} === Processo de configure === ./configure ${CONFIGURE_OPTS} === Compilação (com manuais e contrib) === make world === Instalação === make install-world === Criação de arquivo de serviço SystemD do PostgreSQL === cat << EOF > /lib/systemd/system/postgresql-${PGMAJOR}.service [Unit] Description=PostgreSQL ${PGMAJOR} database server After=syslog.target After=network.target [Service] Type=forking User=postgres Group=postgres Environment=PGDATA="${PGDATA}" Environment=LD_LIBRARY_PATH="${PGHOME}/lib:\${LD_LIBRARY_PATH}" OOMScoreAdjust=-1000 ExecStart=${PGBIN}/pg_ctl start -D \${PGDATA} -s -w -t 300 ExecStop=${PGBIN}/pg_ctl stop -D \${PGDATA} -s -m fast ExecReload=${PGBIN}/pg_ctl reload -D \${PGDATA} -s TimeoutSec=300 [Install] WantedBy=multi-user.target EOF === Dar propriedade ao usuário e grupo postgres aos diretórios === chown -R postgres: /var/{log,local}/pgsql === Criação de cluster (instância) === su - postgres -c "\ initdb \ -D ${PGDATA} \ -E utf8 \ -U postgres \ -k \ --locale=pt_BR.utf8 \ --lc-collate=pt_BR.utf8 \ --lc-monetary=pt_BR.utf8 \ --lc-messages=en_US.utf8 \ -T portuguese \ -X ${PGWAL}" === Alterações no postgresql.conf via sed === # listen_addresses = '*' sed "s:\(^#listen_addresses.*\):\1\nlisten_addresses = '*':g" \ -i ${PGDATA}/postgresql.conf # log_destination = 'stderr' sed "s:\(^#log_destination.*\):\1\nlog_destination = 'stderr':g" \ -i ${PGDATA}/postgresql.conf # logging_collector = on sed "s:\(^#logging_collector.*\):\1\nlogging_collector = on:g" \ -i ${PGDATA}/postgresql.conf # log_filename (nova linha descomentada) sed "s:\(^#\)\(log_filename.*\):\1\2\n\2:g" \ -i ${PGDATA}/postgresql.conf # log_directory = '${PGLOG}' sed "s:\(^#log_directory.*\):\1\nlog_directory = '${PGLOG}':g" \ -i ${PGDATA}/postgresql.conf # stats_temp_directory = '${PG_STAT_TEMP}' sed \ "s:\(^#stats_temp_directory.*\):\1\nstats_temp_directory = '${PG_STAT_TEMP}':g" \ -i ${PGDATA}/postgresql.conf === Montagem da linha para montagem em memória RAM === echo -e \ "\ntmpfs ${PG_STAT_TEMP} tmpfs size=32M,uid=postgres,gid=postgres 0 0"\ >> /etc/fstab === Monta tudo definido em /etc/fstab === mount -a === Habilita e inicializa o serviço do PostgreSQL === systemctl enable --now postgresql-${PGMAJOR} oot@pgsql-director:/tmp/postgresql-14.6# systemctl status postgresql-${PGMAJOR} ● postgresql-14.service - PostgreSQL 14 database server Loaded: loaded (/lib/systemd/system/postgresql-14.service; enabled; vendor preset: enabled) Active: active (running) since Thu 2022-12-08 18:08:18 -03; 10s ago Process: 17766 ExecStart=/usr/local/pgsql/14/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 (code=exited, status=0/SUCCESS) Main PID: 17769 (postgres) Tasks: 8 (limit: 4678) Memory: 15.6M CPU: 109ms CGroup: /system.slice/postgresql-14.service ├─17769 /usr/local/pgsql/14/bin/postgres -D /var/local/pgsql/14/data ├─17770 postgres: logger ├─17772 postgres: checkpointer ├─17773 postgres: background writer ├─17774 postgres: walwriter ├─17775 postgres: autovacuum launcher ├─17776 postgres: stats collector └─17777 postgres: logical replication launcher dez 08 18:08:17 pgsql-director systemd[1]: Starting PostgreSQL 14 database server... dez 08 18:08:18 pgsql-director pg_ctl[17769]: 2022-12-08 18:08:18.091 -03 [17769] LOG: redirecting log output to logging collector process dez 08 18:08:18 pgsql-director pg_ctl[17769]: 2022-12-08 18:08:18.091 -03 [17769] HINT: Future log output will appear in directory "/var/log/pgsql/14". dez 08 18:08:18 pgsql-director systemd[1]: Started PostgreSQL 14 database server. ===== TimescaleDB ===== git clone https://github.com/timescale/timescaledb.git cd timescaledb git checkout 2.8.1 ./bootstrap -DPG_CONFIG=/usr/local/pgsql/14/bin/pg_config cd build && make make install [[https://github.com/timescale/timescaledb/releases|Releases page]] sed "s:\(^#shared_preload_libraries.*\):\1\nshared_preload_libraries = 'timescaledb':g" \ -i ${PGDATA}/postgresql.conf ===== Limpeza de pacote desnecessários ===== apt purge -y ${PKG_RM} ${PKG_DEB} ===== Testando o acesso ===== root@pgsql-director:/tmp/postgresql-14.6# su - postgres postgres@pgsql-director:~$ psql -d postgres -h localhost -U postgres -p 5432 Expanded display is used automatically. psql (14.6) Type "help" for help. postgres=# SELECT version(); version --------------------------------------------------------------------------------------------------- PostgreSQL 14.6 on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit (1 row) postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+------------+------------+----------------------- postgres | postgres | UTF8 | pt_BR.utf8 | pt_BR.utf8 | template0 | postgres | UTF8 | pt_BR.utf8 | pt_BR.utf8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | pt_BR.utf8 | pt_BR.utf8 | =c/postgres + | | | | | postgres=CTc/postgres (3 rows) postgres=# \q postgres@pgsql-director:~$ exit sair ===== Referências ===== * [[https://github.com/timescale/timescaledb/blob/main/docs/BuildSource.md|Building from source]] * [[https://docs.timescale.com/install/latest/self-hosted/installation-source/|Install self-hosted TimescaleDB from source]]