Access the prod database

Backups

See https://github.com/c2corg/v6_api/wiki/Backup-the-prod-database

Making manual changes in the prod database

ssh compose0.exoscale.infra.camptocamp.org
source /opt/c2corg-docker/server-list.env
cd /opt/c2corg-docker/composition/production/postgresql-main/
docker-compose exec --user postgres postgresql psql c2corg

Making read-only requests (eg. select)

Prefer using the read-only DB replica:

ssh compose0.exoscale.infra.camptocamp.org
cd /opt/c2corg-docker/composition/production/postgresql-backup/
docker-compose exec --user postgres postgresql psql c2corg

To get results in a file:

ssh compose0.exoscale.infra.camptocamp.org
cd /opt/c2corg-docker/composition/production/postgresql-backup/
docker-compose exec --user postgres postgresql psql -tA -d c2corg -P pager=off -c "select count(*) from guidebook.routes" > /tmp/routes-count.txt
cat /tmp/routes-count.txt

Remove archived contents that violate terms of use

Connect to the prod database (the main one, not the read-only replica!):

ssh compose0.exoscale.infra.camptocamp.org
source /opt/c2corg-docker/server-list.env
cd /opt/c2corg-docker/composition/production/postgresql-main/

Run the UPDATE SQL query, adapting the document id and version ids:

# For https://www.camptocamp.org/routes/version/<document id>/<lang>/<version id>

docker-compose exec --user postgres postgresql psql -d c2corg -c "update guidebook.documents_locales_archives set description = '_This content has been removed because it was not consistent with Camptocamp''s terms of use._' where id in (select distinct document_locales_archive_id from guidebook.documents_versions where document_id = <document id> and lang = '<lang>' and id in (<version id1, version id2, etc.>))"

Check the changes:

docker-compose exec --user postgres postgresql psql -d c2corg -c "select document_id, description from guidebook.documents_locales_archives where id in (select distinct document_locales_archive_id from guidebook.documents_versions where document_id = <document id> and lang = '<lang>' and id in (<version id1, version id2, etc.>))"

Merging user accounts

For both spammers and anonymized users.

ssh compose0.exoscale.infra.camptocamp.org
source /opt/c2corg-docker/server-list.env
cd /opt/c2corg-docker/composition/production/api0/
docker-compose run --rm api .build/venv/bin/python c2corg_api/scripts/users/merge.py <source id> <target id>

where source_id is the account to merge and target_id the account to keep.

The "compte fermé" account id is 811780.

Exporting data to shapefiles

For instance to get geometries of skitouring outings in Switzerland (area_id 14067):

ssh compose0.exoscale.infra.camptocamp.org
source /opt/c2corg-docker/server-list.env
cd /opt/c2corg-docker/composition/production/postgresql-main && source .env

# Here is the DB to shapefile export command
docker-compose exec --user postgres postgresql pgsql2shp -f /tmp/c2c_outings.shp c2corg "select o.document_id, geom_detail as geom from guidebook.outings as o, guidebook.documents_geometries as g, guidebook.area_associations as a where o.document_id = g.document_id and o.document_id = a.document_id and 'skitouring' = any(o.activities) and g.geom_detail is not null and a.area_id = 14067"

# Several files are actually generated by the command above:
# alex@compose0-4d41a346-0:/opt/c2corg-docker/composition/production/postgresql-main$ docker-compose exec postgresql bash
# root@820732cce0a8:/# ls -l /tmp
# total 20052
# -rw-r--r-- 1 postgres postgres        5 Apr 18 11:03 c2c_outings.cpg
# -rw-r--r-- 1 postgres postgres       33 Apr 18 11:03 c2c_outings.dbf
# -rw-r--r-- 1 postgres postgres      677 Apr 18 11:03 c2c_outings.prj
# -rw-r--r-- 1 postgres postgres 20475564 Apr 18 11:03 c2c_outings.shp
# -rw-r--r-- 1 postgres postgres    18772 Apr 18 11:03 c2c_outings.shx

# Those files are saved inside the postgresql container.
# Here is how to get them in the current filesystem:

export POSTGRESQL_CONTAINER=`docker-compose ps -q postgresql`
export SHAPEFILE=c2c_outings.shp

docker -H $DOCKER_HOST exec -t $POSTGRESQL_CONTAINER cat /tmp/$SHAPEFILE > /tmp/$SHAPEFILE

# set SHAPEFILE for each file (and extension) needed and run the previous command again.

Check opened connections

select pid, query_start, query  from pg_stat_activity where datname = 'c2corg';

To close a connection:

select pg_terminate_backend(pid) from pg_stat_activity where pid = 1234;