postgresql91-server install

[Phillip Koebbe]

I'm not a PostgreSQL expert, but I'll offer what I can. See inline.

On Jan 11, 2012, at 5:51 PM, Érico wrote:

> Hello 
> 
> Thank you all for the help with mysql install and configuration
> 
> It is working now 
> 
> I am jumping now to postgres which is the second db server I need to configure 
> 
> for that I have in my computer : 
> 
> $ port installed
> postgresql91 @9.1.1_1
> postgresql91 @9.1.2_1 (active)
> postgresql91-server @9.1.2_0 (active)
> postgresql_select @0.1_0 (active)
> readline @6.2.000_0 (active)
> 
> I ran : 
> 1. sudo port load postgresql91-server
> 
> 2. it does save in /Libraries/LaunchDeamons :
> $ ls -la /Library/LaunchDaemons/
> total 16
> drwxr-xr-x   4 root  wheel   136 Jan 11 20:29 .
> drwxrwxr-t+ 61 root  admin  2074 Jan  9 21:42 ..
> -rw-r--r--   1 root  wheel   790 Jan  9 21:42 com.google.keystone.daemon.plist
> lrwxr-xr-x   1 root  admin   100 Jan  8 19:35 org.macports.postgresql91-server.plist -> /opt/local/etc/LaunchDaemons/org.macports.postgresql91-server/org.macports.postgresql91-server.plist
>                            
> 3. I run :
> 
> 4. it is like this after steps 1,2 and 3 : 
> 
> bash-3.2# ps aux | grep 'postgres'
> root       374   0.6  0.0  2435120    404 s001  R+    9:39PM   0:00.00 grep postgres
> postgres   308   0.0  0.0  2446484    416   ??  Ss    9:28PM   0:00.10 postgres: stats collector process     
> postgres   307   0.0  0.0  2486920   1580   ??  Ss    9:28PM   0:00.09 postgres: autovacuum launcher process     
> postgres   306   0.0  0.0  2486788    516   ??  Ss    9:28PM   0:00.11 postgres: wal writer process     
> postgres   305   0.0  0.0  2486788    784   ??  Ss    9:28PM   0:00.14 postgres: writer process     
> postgres   303   0.0  0.2  2486788   7052 s000  S     9:28PM   0:00.18 /opt/local/lib/postgresql91/bin/postgres -D /opt/local/var/db/postgresql91/testdb
> postgres   295   0.0  0.0  2435548   1008 s000  S+    9:28PM   0:00.06 bash
> root       294   0.0  0.1  2467416   4016 s000  S     9:27PM   0:00.02 su postgres
> root       325   0.0  0.0  2455108   1044   ??  Ss    9:31PM   0:00.01 /opt/local/bin/daemondo --label=postgresql91-server --start-cmd /opt/local/etc/LaunchDaemons/org.macports.postgresql91-server/postgresql91-server.wrapper start ; --stop-cmd /opt/local/etc/LaunchDaemons/org.macports.postgresql91-server/postgresql91-server.wrapper stop ; --restart-cmd /opt/local/etc/LaunchDaemons/org.macports.postgresql91-server/postgresql91-server.wrapper restart ; --pid=none
> 
> 
> 5. sudo mkdir -p /opt/local/var/db/postgresql91/testdb  
>   

When you make this directory, you're making an actual database. Rather, you're making a data directory that PostgreSQL will use to store data.

> 
> 6. sudo chown postgres:postgres /opt/local/var/db/postgresql91/testdb
> 
> 
> 7. sudo su postgres -c '/opt/local/lib/postgresql91/bin/initdb -D /opt/local/var/db/postgresql91/testdb'
> 
> bash-3.2# sudo su postgres -c '/opt/local/lib/postgresql91/bin/initdb -D /opt/local/var/db/postgresql91/testdb'
> The files belonging to this database system will be owned by user "postgres".
> This user must also own the server process.
> ....
> Success. You can now start the database server using:
> 
>     /opt/local/lib/postgresql91/bin/postgres -D /opt/local/var/db/postgresql91/testdb
> or
>     /opt/local/lib/postgresql91/bin/pg_ctl -D /opt/local/var/db/postgresql91/testdb -l logfile start
> ….

-D is telling Postgres which data *directory* to use, not database.

> 
> 8. in a second terminal as postgres id :
> 
> bash-3.2$ /opt/local/lib/postgresql91/bin/pg_ctl -D /opt/local/var/db/postgresql91/testdb -l /opt/apache/db/logfile start
> server starting
> ... 
> 
> 9. then in the same terminal as  postgres id :
> bash-3.2$ psql -d testdb

At this point, you do not have a testdb database. In order to create one, you'd need to connect to one of the default databases: postgres, template0, template1. I usually connect to postgres. Then you'd do

> create database testdb;
> \c testdb



> psql: FATAL:  could not open relation mapping file "global/pg_filenode.map": No such file or directory
> 

I'm not sure what this is about. Normally if you try to connect to a database that doesn't exist, you'll get a very helpful message saying it doesn't exist. This message may have something to do with the fact that you tried to connect to a database that has the same name as the data directory, but I'm not sure. Someone with more intimate knowledge of PG will need to address this.

> 10. in log file I have several lines with this content : 
> LOG:  could not open temporary statistics file "pg_stat_tmp/pgstat.tmp": No such file or directory
> 
>  
> 
> Can you guys help us out on this one too please ?
> 

Try connecting to the postgres database and see what happens. If you get connected successfully, create a new database. You might not want to use the same name as the data directory (testdb) though. I'm not sure what might happen. Try putting an underscore in it (test_db) and see what happens.

Phillip