Activate user

For some reason activation of user accounts sometimes fails.

To manually activate a user account:

Log in as user postgres
sudo -i -u postgres

Start psql
psql

Connect to the galaxy database (use \l to show all databases):
\c galaxydb

Show table 'galaxy_user' (use \d to show all tables in db):
TABLE galaxy_user;

Show row(s) in 'galaxy_user' that have fozzy.bear in email-address:
SELECT * FROM galaxy_user WHERE email LIKE '%fozzy.bear%';

Activate user fozzy bear:
UPDATE galaxy_user SET active='t' WHERE email='fozzy.bear@naturalis.nl';

PSQL as ubuntu

Show inactive accounts:
sudo -u postgres -H -- psql -d galaxydb -c "SELECT email,username,active FROM galaxy_user WHERE active='f';"

Activate user fozzy bear:
sudo -u postgres -H -- psql -d galaxydb -c "UPDATE galaxy_user SET active='t' WHERE email='fozzy.bear@naturalis.nl';"

---

About PSQL

With multipage output use:
f to scroll forward
b to scroll back
q to return to the command prompt

Show only column names of table:
SELECT column_name FROM information_schema.columns WHERE table_name='galaxy_user';

---

Export TABLE (or SELECTION) as CSV

Always use full pathnames with the COPY command (PSQL)
Remember you are logged in as user postgres for which the working directory is:
/var/lib/postgresql so you don't have permission to write outside that folder
(e.g. /home/ubuntu)

Toggle visibility of column names before making a query:
\t
Show email, username, disk_usage and whether an account is deleted,purged or active:
SELECT email,username,disk_usage,deleted,purged,active FROM galaxy_user;

To export the galaxy_user table as CSV:
COPY galaxy_user TO '/var/lib/postgresql/galaxy_user.csv' CSV;

To export only the email, username and disk_usage:
COPY (SELECT email,username,disk_usage FROM galaxy_user) TO '/var/lib/postgresql/galaxy_usage.csv' CSV;

---

PSQL and BASH

Depending on what should be done with query result it might be more useful
to pipe the results to stdout. An example of PSQL and BASH is shown in User statistics