PostgreSQL
Peering Manager requires a PostgreSQL (>= 12) database to store data. This can be hosted locally or on a remote server. Please note that Peering Manager does not support any other database backends as it uses some specific features of PostgreSQL.
Installation
# apt update
# apt install postgresql libpq-dev
# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# yum install postgresql13-server
# /usr/pgsql-13/bin/postgresql-13-setup initdb
# systemctl enable postgresql-13 --now
# yum install postgresql-server
# postgresql-setup --initdb --unit postgresql
# systemctl enable postgresql --now
Attention
Depending on your distribution, you may have to edit your PostgreSQL
configuration to allow logging in with a supported mechanism.
The configuration file is usually located at /var/lib/pgsql/version/data/pg_hba.conf
and has to allow password, trust or peer authentication. Others may work but
the previously mentioned are the most trivial to use. Please note that especially
trust can result in security risks and should only be used if you
know what you are doing.
Please see the PostgreSQL documentation
for more information.
Database Creation
At a minimum, we need to create a database for Peering Manager and assign it a username and password for authentication.
# sudo -u postgres psql
psql (12.15)
Type "help" for help.
postgres=# CREATE DATABASE peering_manager ENCODING 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TEMPLATE template0;
CREATE DATABASE
postgres=# CREATE USER peering_manager WITH PASSWORD 'DoNotUseMe';
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE peering_manager TO peering_manager;
GRANT
-- If running PostgreSQL v15 or above the following two commands are also necessary:
\connect peering_manager
GRANT CREATE ON SCHEMA public TO peering_manager;
postgres=# \q
Attention
When creating the database on Debian fails with the message
ERROR: invalid locale name: "en_US.UTF-8", you are missing the locale.
Run dpkg-reconfigure locales and select en_US.UTF-8 to generate it.
Then restart the database server by running systemctl restart postgresql.service.
You can test that authentication works with the following command. (Replace
localhost with your database server if using a remote database.)
# psql -U peering_manager -W -h localhost peering_manager
If successful, you will see a peering_manager prompt. Type \q to exit.
Migrating encoding to UTF-8
If your database was created with another encoding than UTF-8, you will need to migrate it. To convert the database you have to drop it and re-create it. It's not mandatory but you may face some issues if the encoding of your database is not set to UTF-8.
$ pg_dump --encoding utf8 peering_manager -f peering_manager.sql
postgres=# DROP DATABASE peering_manager;
DROP DATABASE
postgres=# CREATE DATABASE peering_manager ENCODING 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TEMPLATE template0;
CREATE DATABASE
postgres=# GRANT ALL PRIVILEGES ON DATABASE peering_manager TO peering_manager;
GRANT
postgres=# \q
$ psql -f peering_manager.sql -d peering_manager