Configuración de EDB Postgresql en Rocky Linux 8 luego de instalado

Categorías:

Una vez tenemos instalado el software de EDB Postgresql debemos configurarlo, esto es, definir el directorio donde el motor de base de datos va a almacenar la información de tablas y bases de datos que se irán creando con el tiempo.

En este caso en particular, vamos a hacer uso del directorio /datos/edb/as12.

En la ruta anterior se creará toda la estructura con la que trabajará EDB Postgresql.

[root@rocky88 ~]# PGSETUP_INITDB_OPTIONS="-D /datos/edb/as12 -E UTF-8" /usr/edb/as12/bin/edb-as-12-setup initdb
Initializing database ... failed, see /var/lib/edb/as12/initdb.log

[root@rocky88 ~]#

La salida del comando PGSETUP_INITDB_OPTIONS=”-D /datos/edb/as12 -E UTF-8″ /usr/edb/as12/bin/edb-as-12-setup initdb indica que hubo un error. Sin embargo cuando se revisa el archivo de registro que genera el proceso, no se obtiene error.

Revisemos el archivo generado.

[root@rocky88 ~]# cat /var/lib/edb/as12/initdb.log 
The files belonging to this database system will be owned by user "enterprisedb".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /datos/edb/as12 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/Bogota
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
creating edb sys ... ok
loading edb contrib modules ...
edb_redwood_bytea.sql ok
edb_redwood_date.sql ok
dbms_alert_public.sql ok
dbms_alert.plb ok
dbms_job_public.sql ok
dbms_job.plb ok
dbms_lob_public.sql ok
dbms_lob.plb ok
dbms_output_public.sql ok
dbms_output.plb ok
dbms_pipe_public.sql ok
dbms_pipe.plb ok
dbms_rls_public.sql ok
dbms_rls.plb ok
dbms_sql_public.sql ok
dbms_sql.plb ok
dbms_utility_public.sql ok
dbms_utility.plb ok
dbms_aqadm_public.sql ok
dbms_aqadm.plb ok
dbms_aq_public.sql ok
dbms_aq.plb ok
dbms_profiler_public.sql ok
dbms_profiler.plb ok
dbms_random_public.sql ok
dbms_random.plb ok
dbms_redact_public.sql ok
dbms_redact.plb ok
dbms_lock_public.sql ok
dbms_lock.plb ok
dbms_scheduler_public.sql ok
dbms_scheduler.plb ok
dbms_crypto_public.sql ok
dbms_crypto.plb ok
dbms_mview_public.sql ok
dbms_mview.plb ok
dbms_session_public.sql ok
dbms_session.plb ok
edb_bulkload.sql ok
edb_gen.sql ok
edb_objects.sql ok
edb_redwood_casts.sql ok
edb_redwood_strings.sql ok
edb_redwood_views.sql ok
utl_encode_public.sql ok
utl_encode.plb ok
utl_http_public.sql ok
utl_http.plb ok
utl_file.plb ok
utl_tcp_public.sql ok
utl_tcp.plb ok
utl_smtp_public.sql ok
utl_smtp.plb ok
utl_mail_public.sql ok
utl_mail.plb ok
utl_url_public.sql ok
utl_url.plb ok
utl_raw_public.sql ok
utl_raw.plb ok
commoncriteria.sql ok
waitstates.sql ok
installing extension edb_dblink_libpq ... ok
installing extension edb_dblink_oci ... ok
installing extension pldbgapi ... ok
snap_tables.sql ok
snap_functions.sql ok
dblink_ora.sql ok
sys_stats.sql ok
finalizing initial databases ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    /usr/edb/as12/bin/pg_ctl -D /datos/edb/as12 -l logfile start

[root@rocky88 ~]# 

Ahora, debemos crear un archivo que se encargue del inicio y detención de forma automática del servicio de EDB. Para ello, vamos a crear el siguiente archivo

/etc/systemd/system/edb-as-12.service

Para este fin, vamos a copiar un archivo que EDB tiene generado por defecto con la instalación del software y vamos a modificar algunos apartes.

[root@rocky88 ~]# cp  /usr/lib/systemd/system/edb-as-12.service /etc/systemd/system/edb-as-12.service
[root@rocky88 ~]# 

Ahora, veremos el contenido del archivo original /usr/lib/systemd/edb-as-12.service

[root@rocky88 ~]# cat /usr/lib/systemd/system/edb-as-12.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  If you want to customize, the
# best way is to create a file "/etc/systemd/system/edb-as-12.service",
# containing
#	.include /lib/systemd/system/edb-as-12.service
#	...make your changes here...
# For more info about custom unit files, see
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F

# Note: changing PGDATA will typically require adjusting SELinux
# configuration as well.

[Unit]
Description=EDB Postgres Advanced Server 12
After=syslog.target network.target

[Service]
Type=notify

User=enterprisedb
Group=enterprisedb

# Location of database directory
Environment=PGDATA=/var/lib/edb/as12/data
PIDFile=/var/lib/edb/as12/data/postmaster.pid

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000

ExecStartPre=/usr/edb/as12/bin/edb-as-12-check-db-dir ${PGDATA}
ExecStart=/usr/edb/as12/bin/edb-postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT

# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300

[Install]
WantedBy=multi-user.target
[root@rocky88 ~]#

El archivo /etc/systemd/system/edb-as-12.service queda entonces con 3 líneas diferentes

include, Environment y PIDFILE

include=/lib/systemd/system/edb-as-12.service

Environment=PGDATA=/datos/edb/as12

PIDFile=/datos/edb/as12/postmaster.pid

 

[root@replicaedbpdn ~]# cat  /etc/systemd/system/edb-as-12.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  If you want to customize, the
# best way is to create a file "/etc/systemd/system/edb-as-12.service",
# containing
#	.include /lib/systemd/system/edb-as-12.service
#	...make your changes here...
# For more info about custom unit files, see
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F

# Note: changing PGDATA will typically require adjusting SELinux
# configuration as well.

include=/lib/systemd/system/edb-as-12.service

[Unit]
Description=EDB Postgres Advanced Server 12
After=syslog.target network.target

[Service]
Type=notify

User=enterprisedb
Group=enterprisedb

# Location of database directory
Environment=PGDATA=/datos/edb/as12
PIDFile=/datos/edb/as12/postmaster.pid

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000

ExecStartPre=/usr/edb/as12/bin/edb-as-12-check-db-dir ${PGDATA}
ExecStart=/usr/edb/as12/bin/edb-postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT

# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300

[Install]
WantedBy=multi-user.target
[root@replicaedbpdn ~]# 

Una vez se han realizado estas alteraciones, procedemos con el siguiente paso…

[root@rocky88 ~]# systemctl daemon-reload
[root@rocky88 ~]#

Luego de ello, iniciamos el servicio de edb-as-12 correspondiente al motor EDB Postgresql

[root@rocky88 ~]# systemctl enable edb-as-12
[root@rocky88 ~]# systemctl start edb-as-12
[root@rocky88 ~]#

Verificamos el servicio en ejecución

[root@rocky88 ~]# systemctl status edb-as-12
● edb-as-12.service - EDB Postgres Advanced Server 12
   Loaded: loaded (/etc/systemd/system/edb-as-12.service; disabled; vendor preset: disabled)
   Active: active (running) since Sun 2023-05-21 14:05:53 -05; 4s ago
  Process: 39397 ExecStartPre=/usr/edb/as12/bin/edb-as-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 39403 (edb-postmaster)
    Tasks: 9 (limit: 23208)
   Memory: 51.0M
   CGroup: /system.slice/edb-as-12.service
           ├─39403 /usr/edb/as12/bin/edb-postmaster -D /datos/edb/as12
           ├─39404 postgres: logger   
           ├─39406 postgres: checkpointer   
           ├─39407 postgres: background writer   
           ├─39408 postgres: walwriter   
           ├─39409 postgres: autovacuum launcher   
           ├─39410 postgres: stats collector   
           ├─39411 postgres: dbms_aq launcher   
           └─39412 postgres: logical replication launcher   

may 21 14:05:52 rocky88.unixpad.local systemd[1]: Starting EDB Postgres Advanced Server 12...
may 21 14:05:53 rocky88.unixpad.local edb-postmaster[39403]: 2023-05-21 14:05:53 -05 LOG:  starting PostgreSQL 12.15 (EnterpriseDB Advanced Server 12.15.19) on x86_64-pc-linux-gnu, compiled by gcc >
may 21 14:05:53 rocky88.unixpad.local edb-postmaster[39403]: 2023-05-21 14:05:53 -05 LOG:  listening on IPv4 address "0.0.0.0", port 5444
may 21 14:05:53 rocky88.unixpad.local edb-postmaster[39403]: 2023-05-21 14:05:53 -05 LOG:  listening on IPv6 address "::", port 5444
may 21 14:05:53 rocky88.unixpad.local edb-postmaster[39403]: 2023-05-21 14:05:53 -05 LOG:  listening on Unix socket "/tmp/.s.PGSQL.5444"
may 21 14:05:53 rocky88.unixpad.local edb-postmaster[39403]: 2023-05-21 14:05:53 -05 LOG:  redirecting log output to logging collector process
may 21 14:05:53 rocky88.unixpad.local edb-postmaster[39403]: 2023-05-21 14:05:53 -05 HINT:  Future log output will appear in directory "log".
may 21 14:05:53 rocky88.unixpad.local systemd[1]: Started EDB Postgres Advanced Server 12.
[root@rocky88 ~]# 

Como podemos ver, el servicio se ha ejecutado exitósamente.

Ya tenemos nuestro motor listo para poblarse con nuestras bases de datos.