Database Server

Check Postgresql software page for more information about database solution, read postgresql documentation on how to install and configure.

What is the PostgreSQL database top capacity?

Maximum Row Size: 1.6 TB
Maximum Field Size: 1 GB
Maximum Rows per Table: Unlimited
Maximum Columns per Table: 250 - 1600 depending on column types


Upgrade

10 / 11 to 12

Simpler way to upgrade Postgresql version 10 / 11 to 12 is to dump postgresql data with previous version and then import with new version.

Download previous packages for dumping;


Data directory

Data directory depends on user and rc init script, check data directory before starting the server. Check user home directory;

$ getent passwd postgres | cut -f 6 -d :
/var/pgsql/data
$

Check rc script data directory;

$ grep PG_DATA= /etc/rc.d/postgresql
PG_DATA=/var/pgsql/data
$

If the data directory is not desired one;

# usermod -m -d /srv/pgsql/data postgres

And change PG_DATA=/var/pgsql/data on /etc/rc.d/postgresql.

# sed -i 's+PG_DATA=\/var\/pgsql\/data+PG_DATA=/srv/pgsql/data+g' /etc/rc.d/postgresql


Log

Create default log file;

# touch /var/log/postgresql
# chown postgres:postgres /var/log/postgresql

Auth. and Permissions

Allow user to become postgres without password;

# sudoedit /etc/sudoers

And add following line;

%users  ALL = NOPASSWD: REBOOT

user_name ALL=(postgres:postgres) NOPASSWD:ALL

Configuration setting password_encryption defines default ENCRYPTED UNCRYPTED options when using CREATE ROLE or ALTER ROLE to change password.

#alter user postgres with password 'new_password';


add/view comments