Edit the pg_hba.conf file
The first changes to make involve the pg_hba.conf file and the postgres.conf file. To do so, you'll need to use VI (a text editor) and be the postgres user in terminal. To do this, start terminal and type
enter the
Go to the postgres data directory by typing
Edit the postgres pg_hba.conf file that contains the addreses to listen on. Type
You should see a page of information. If you do not, then type '' and then just a '' - after which you can start the process over. If you see the list of text similar to the right, then:
Use the on your keyboard to go to the very end of the file
When you are at the end of the file, use the up arrow on your keyboard so that you are right after the first line in the IPv4 settings there it says 'host all all 127.0.0.1/32 trust'. In the example the cursor is on the '#' on the line after.
type the
key and the message at the bottom will change to Insert.
Edit the pg_hba.conf so that its final settings are similar to the window on the right
simply type things so that data looks like the window and use the delete key to get rid of characters. You will likely end up typing the following lines where the first one is your subnet and this is just the most typical example we've seen at venues
NOTE: for the 127.0.0.1/32 option, leave the handshaking to be TRUST at the end of the line to allow backups to run unaided.
NOTE: if your machine uses IPv6 (the new internet IP setting standard), you may also need to set ::1/128 to also be TRUST instead of md5. If so, edit that line to look like
The line you added (or need to add) are for:
- The local subnet - as in 192.168.9.0/24
- Other subnets that need to access the data - as in 10.1.5.0/24
- Any single machines that must have access - as in 55.66.77.88/32 (via VPN)
At the end of the subnet, the /24 refers to a complete subnet when you want any machine on the subnet to access the database. This is what is used most often.
The /32 refers to a particular machine. If you use this option, you will need to provide the exact computer IP that you want to allow to access the database.
At the end, type, in this order:
hit the '' key
(the insert mode will dissappear)
(the window will show the 'Entering Ex mode' message)
and the window will clear.
Reference for settings in the pg hba.conf file www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html
Edit the postgesql.conf configuration file
This will also use VI to edit it. Type
when the list appears, type
to go into insert mode and use the up and down arrows to find the options below
Find and edit the parameters in the list below and change them to the suggested values, if they are not already set to that value.
Note: if any line contains a '#' at the beginning and you need to change that line as per the instructions below, make sure to remove the '#' as it uncomments the parameter. If there is no '#', then just change the values.
For any setting that is about disk space or memory, you can type 1GB, 1000MB, 1000000KB and they are the equivalent. Do not leave a space between the number and the memory amount at the GB, MB or KB otherwise postgres will not start.
| listen_addresses | This value should always = It will probably look like #Listen_address = 'localhost'. Remove the # from the front of the line (if any) to activate that parameter and chnage 'localthost' to '*' |
| max_connection | This is the number of simultaneous connections. Change it from the default (3) to |
| effective_cache_size | This value should be roughly the amount of 'available' memory (always round down to the nearest 50 MB) as seen in the Activity Monitor without PostgreSQL running. If there are other applications that will also run, you want to subtract their memory footprint. It is good to take the reading before installing PostgreSQL. |
| maintenance_work_mem | This value should be for machines with 1 GB of RAM or more and for those with less. Enter values as xxMB. |
| shared_buffers | This value should be 20-25% of the total system total RAM. You find this value on the task manager as the total phyiscal memory. Enter values as . |
| temp_buffers | This value should be for machines with 2GB of RAM or more and for those with less. Enter values as xxMB. |
| work_mem | This value should be for machines with 1GB of RAM or more and for those with less. Enter values as xxMB. |
Reference for postgres.conf file parameters http://www.postgresql.org/docs/8.4/interactive/runtime-config.html
Once the changes are made, type, in this order:
hit the '' key
(the insert mode will dissappear)
(the window will show the 'Entering EX mode' message)
and the window wll clear and you will be back at terminal
Set up terminal bash_profile
Next, lets update the profile for terminal to that it makes life easier in postgres from this point on. Type
it will open with an empty window as below. Just like when using VI for the other two files, type:
to put you in insert mode
Type the two lines into the file exactly as shown. When done, type, in this order:
hit the '' key
(the insert mode will dissappear)
(the window will show the 'Entering Ex mode' message)
and the window will clear and you will be back at terminal. The next time you start terminal under the postgres user account, you will have access to the postgres commands and data directory in a more convenient fashion.
Change OS X Shared Memory Settings
BEFORE STARTING: Ensure you are at your own user, not postgres. If you are still in terminal, to get back to your user name, type the following. Otherwise just open terminal again. This is a fairly technical topic and the reference: http://www.postgresql.org/docs/8.3/interactive/kernel-resources.html
Type
Type to insert data
Add the lines:
Hit the key
Hit
Type
Hit
Restart OS X.
Note there is a sample of this file in /users/Shared/ from the install. If your machine has 2GB of RAM or more, you could move it to the right folder instead by:
Restart OS X after doing this.