docs/modules/sysadmin_sops/pages/database.adoc

= Database Infrastructure SOP

Our database servers provide database storage for many of our apps.

== Contents

* <<_contact_information>>
* <<_description>>
* <<_creating_a_new_postgresql_database>>
* <<_troubleshooting_and_resolution>>
** <<_connection_issues>>
** <<_some_useful_queries>>
*** <<_what_queries_are_running>>
*** <<_seeing_how_dirty_a_table_is>>
*** <<_xid_wraparound>>
** <<_restart_procedure>>
*** <<_koji>>
*** <<_bodhi>>
* <<_turbogears_and_mysql>>
* <<_restoring_from_backups_or_specific_dbs>>

== Contact Information

Owner::
  Fedora Infrastructure Team
Contact::
  #fedora-admin, sysadmin-main, sysadmin-dba group
Location::
  rdu3
Servers::
  db01, db03, db-fas01, db-datanommer02, db-koji01, db-riscv-koji01, db-openqa01
Purpose::
  Provides database connection to many of our apps.

== Description

_db01_, _db03_ and _db-fas01_ are our primary servers. _db01_ and _db-fas01_ run
_PostgreSQL_. _db03_ contain _mariadb_. _db-koji01_, _db-riscv-koji01_,
contain _kojis_. _db-qa01_ and _db-openqa01 contain _openqa.
_db-datanommer02_ contains all fedora messaging messages.

== Creating a New Postgresql Database

Creating a new database on our postgresql server isn't hard but there's
several steps that should be taken to make the database server as secure
as possible.

We want to separate the database permissions so that we don't have the
user/password combination that can do anything it likes to the database
on every host (the webapp user can usually do a lot of things even
without those extra permissions but every little bit helps).

Say we have an app called "raffle". We'd have three users:

* raffleadmin: able to make any changes they want to this particular
database. It should not be used in day to day but only for things like
updating the database schema when an update occurs. We could very likely
disable this account in the db whenever we are not using it.

* raffleapp: the database user that the web application uses. This will
likely need to be able to insert and select from all tables. It will
probably need to update most tables as well. There may be some tables
that it does _not_ need delete on. It should almost certainly not need
schema modifying permissions. (With postgres, it likely also needs
permission to insert/select on sequences as well).

* rafflereadonly: Only able to read data from tables, not able to modify
anything. Sadly, we aren't using this often but it can be useful for
scripts that need to talk directly to the database without modifying it.

....
db2 $ sudo -u postgres createuser -P -E NEWDBadmin
Password: <randomly generated password>
db2 $ sudo -u postgres createuser -P -E NEWDBapp
Password: <randomly generated password>
db2 $ sudo -u postgres createuser -P -E NEWDBreadonly
Password: <randomly generated password>
db2 $ sudo -u postgres createdb -E utf8 NEWDB -O NEWDBadmin
db2 $ sudo -u postgres psql NEWDB
NEWDB=# revoke all on database NEWDB from public;
NEWDB=# revoke all on schema public from public;
NEWDB=# grant all on schema public to NEWDBadmin;
NEWDB=# [grant permissions to NEWDBapp as appropriate for your app]
# Example: grant select, insert, update, delete on schema public to NEWDBapp;
NEWDB=# [grant permissions to NEWDBreadonly as appropriate for a user that
       is only trusted enough to read information]
# Example: grant select on schema public to NEWDBreadonly;
NEWDB=# grant connect on database NEWDB to nagiosuser;
....

If your application needs to have the NEWDBapp and password to connect
to the database, you probably want to add these to ansible as well. Put
the password in the private repo in batcave01. Then use a templatefile
to incorporate it into the config file. See fas.pp for an example.

== Troubleshooting and Resolution

=== Connection issues

There are no known outstanding issues with the database itself. Remember
that every time either database is restarted, services may have to be
restarted (see <<_restart_procedure>>).

=== Some useful queries

==== What queries are running

This can help you find out what queries are cuurently running on the
server:

....
select datname, pid, query_start, backend_start, query from
pg_stat_activity where state<>'idle' order by query_start;
....

This can help you find how many connections to the db server are for
each individual database:

....
select datname, count(datname) from pg_stat_activity group by datname
order by count desc;
....

print sizes of tables in currently connected database:

....
select table_name, pg_size_pretty(pg_total_relation_size(quote_ident(table_name))), pg_total_relation_size(quote_ident(table_name)) from information_schema.tables where table_schema = 'public' order by 3 desc;
....

==== Seeing how "dirty" a table is

We've added a function from postgres's contrib directory to tell how
dirty a table is. By dirty we mean, how many tuples are active, how many
have been marked as having old data (and therefore "dead") and how much
free space is allocated to the table but not used.:

....
\c fas2
\x
select * from pgstattuple('visit_identity');
table_len          | 425984
tuple_count        | 580
tuple_len          | 46977
tuple_percent      | 11.03
dead_tuple_count   | 68
dead_tuple_len     | 5508
dead_tuple_percent | 1.29
free_space         | 352420
free_percent       | 82.73
\x
....

Vacuum should clear out dead_tuples. Only a vacuum full, which will lock
the table and therefore should be avoided, will clear out free space.

== Restart Procedure

If the database server needs to be restarted it should come back on its
own. Otherwise each service on it can be restarted:

....
service mysqld restart
service postgresql restart
....

== Restoring from backups or specific dbs.

Our backups store the latest copy in `/backups/` on each db server. These
backups are created automatically by the db-backup script run fron cron.
These backups are synced daily to backup01. If you need a backup from
a while ago or the host is no longer available, they will be there.
Look in `/usr/local/bin` for the backup script.

To restore partially or completely you need to:

[arabic]
. setup postgres on a system
+
. start postgres/run initdb
+
if this new system running postgres has already run ansible then it
will have wrong config files in `/var/lib/pgsql/data` - clear them out
before you start postgres so initdb can work.
+
. grab the backups you need from `/backups` - also grab _global.sql_
+
edit up _global.sql_ to only create/alter the dbs you care about
+
. as postgres run: `psql -U postgres -f global.sql`
. when this completes you can restore each db with (as postgres user)
+
`createdb $dbname pg_restore -d dbname dbname_backup_file.db`
+
. restart postgres and check your data.

== modules and versioning

In the case of some servers we are using a postgresql module in rhel
in order to run a newer version. Check the /etc/dnf/modules.d/postgresql.module
file to determine what stream is active.