|
Size: 888
Comment: postgres is definitely not in afs any more
|
Size: 1333
Comment: look ma, it's not outdated anymore, just not very useful!
|
| Deletions are marked like this. | Additions are marked like this. |
| Line 1: | Line 1: |
| See [[MySQL]] for list of things that have to be done for any database (both MySQL and Postgres). |
Warning: postgresql configuration is not controlled by a Debian package, but it should be. Ideally `pg_hba.conf` and `pg_ident.conf` would be managed by DomTool, or some other system management daemon. |
| Line 4: | Line 3: |
| = Postgres-specific setup = | Current node is fritz, with Postgres 8.1 on port 5422, and Postgres 9.1 on 5433. Note that navajos/bog can only use Postgres 9.1, and support for 8.1 will be removed once mire has been decommissioned. |
| Line 6: | Line 5: |
| 1. $dir = /afs/hcoop.net/common/databases/USERNAME/postgres | The source code of DomTool's dbms module is useful as documentation. |
| Line 8: | Line 7: |
| If $dir does NOT exist: | == Tablespaces == |
| Line 10: | Line 9: |
| 1. sudo -u postgres psql -c "CREATE USER '''USERNAME'''" template1 1. mkdir -p $dir 1. chown postgres:postgres "$dir" 1. fs setacl -dir $dir -acl postgres write 1. fs setacl -dir $dir -acl databases none # (keep out other databases, just in case) 1. fs setacl -dir $dir -acl system:backup rl # (should be inherited from parent dir) 1. sudo -u postgres psql -c "CREATE TABLESPACE user_'''USERNAME''' OWNER '''USERNAME''' LOCATION '$dir'" template1 |
Each user has a table space in `/srv/database/$PATHBITS/$USER/{postgres,postgres-9.1}`, created by the `create-user-database` script. |
| Line 18: | Line 11: |
| When it does, go directly to database creation step: | Tablespaces are an artifact from when we stored databases in afs. There may be some organizational advantage as well, but future admins should revisit the issue. |
| Line 20: | Line 13: |
| 1. sudo -u postgres createdb -O '''USERNAME''' -D user_'''USERNAME''' '''DBNAME''' | == Authentication == The `pg_hba.conf` of every install must explicitely list the allowed hosts. Firewall rules on both sides should be opened. Postgres 8.1 uses ident. It's a hack, but GSSAPI support wasn't quite functional enough, or at least we'll say it wasn't. Postgres 9.1 uses GSSAPI. An `pg_ident.conf` is used to map `$USER/daemon@HCOOP.NET` to the Postgres user `$USER`. This has the advantage that `$USER@HCOOP.NET` resolves to the same database user. |
| Line 22: | Line 22: |
| CategoryOutdated CategoryNeedsWork | CategoryNeedsWork CategorySystemAdministration |
Warning: postgresql configuration is not controlled by a Debian package, but it should be. Ideally pg_hba.conf and pg_ident.conf would be managed by DomTool, or some other system management daemon.
Current node is fritz, with Postgres 8.1 on port 5422, and Postgres 9.1 on 5433. Note that navajos/bog can only use Postgres 9.1, and support for 8.1 will be removed once mire has been decommissioned.
The source code of DomTool's dbms module is useful as documentation.
1. Tablespaces
Each user has a table space in /srv/database/$PATHBITS/$USER/{postgres,postgres-9.1}, created by the create-user-database script.
Tablespaces are an artifact from when we stored databases in afs. There may be some organizational advantage as well, but future admins should revisit the issue.
2. Authentication
The pg_hba.conf of every install must explicitely list the allowed hosts. Firewall rules on both sides should be opened.
Postgres 8.1 uses ident. It's a hack, but GSSAPI support wasn't quite functional enough, or at least we'll say it wasn't.
Postgres 9.1 uses GSSAPI. An pg_ident.conf is used to map $USER/daemon@HCOOP.NET to the Postgres user $USER. This has the advantage that $USER@HCOOP.NET resolves to the same database user.