7,612
edits
Changes
Jump to navigation
Jump to search
or(runs the server with output on the terminal) /usr/local/pgsql/bin/pg_ctl postgres -D /usr/local/pgsql/data -l >/usr/local/pgsql/data/logfile start 2>&1 & Note that (runs the second command may fail silently if there is something wrong server with output in the config, and logfile - note the first uses path is needed for the terminal unless you add logfile) /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/data/logfile start & (same as above but using the &.pg_ctl wrapper)
→Find the sizes of databases on our postgres server
[[category:McNair Admin]]
See also:
*[[Working with PostgreSQL]]
*[[Haas PhD Server Configuration]]
Note that the server is now on 128.32.204.203 (having been moved to the new colo)
==Mount Bear==
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
Add to /etc/rc.local
su -c '/usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data -l logfile -D >/usr/local/pgsql/data/serverlog' postgres
Start postgres with one the following commands as the postgres user (if you've fixed the path then abbrev the first part)
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
Check postgres is listening on 5432:
Test by connecting remotely using a psql client on your desktop. Then enjoy!
psql -h 128.32.252.201 -U ed_egan test
==Adding Users==
===Adding Root Accounts to the box===
Assuming that you have root, you can create user accounts on the box and give them root too. This isn't necessary for regular users - they just need a Postgres user account (see below). To add users to the box, the process is:
First great the users group, checking the last group number (5xx is the next one):
cat /etc/group
/usr/sbin/groupadd -g 5xx username
Then add the user (it doesn't matter what you put for -p, it is going to be overwritten):
/usr/sbin/useradd -g username -G root -s /bin/bash -p xxxx -d /home/username -m username
where g is the primary group, G is other groups, p sets a password,
d declares a home directory and m makes the directory
Change the user's password:
passwd username
And add the user to the sudoers file
echo 'username ALL=(ALL) ALL' >> /etc/sudoers
====Deleting a user====
To delete a user:
/usr/sbin/userdel -r roger
where r removes the home directory
And to remove their group
/usr/sbin/groupdel username
And remove their entry from the sudoers file too if they had root.
===Adding Postgres Accounts===
Log on to the box as root then:
su postgres
To work as the postgres account.
Now add the user using:
/usr/local/pgsql/bin/createuser username
(Answer y or n to whether you want the new role to be superuser - generally y)
If the user doesn't have an account on the box, then you'll need to add a database for them (use DBname of 'firstname_data' as a default):
/usr/local/pgsql/bin/createdb -O username DBname
==Size, Backup & Restore==
===Find the sizes of databases on our postgres server===
*Connect to the dbase server (poss. with an admin account)
*psql postgres
*run the following query:
SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE 'No Access'
END AS SIZE
FROM pg_catalog.pg_database d
ORDER BY
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_database_size(d.datname)
ELSE NULL
END DESC -- nulls first
LIMIT 20;
To see the disk space use on all drives use:
df -h
===Backing up a dbase===
pg_dump dbase > dbase.dump
or
pg_dump -Fc dbase > dbase_fc.dump (which uses compression)
Note: Use top or ps -aux to see memory/CPU usage.
We can also manually compress/decompress using:
gzip filename
gzip -d filename.gz
When done, we can drop the database:
dropdb dbase
===Restoring a dbase===
pg_restore -d newdb dbase.dump
[[admin_classification::IT Build| ]]
