Postgresql on dreamhost vps solved

#1

it took me a couple of hours to figure this out so i figured i would post it here and maybe save somebody else a few minutes. comments welcome. thanks.

  1. install postgresql:
  1. give postgres user a password:
  1. enter ineractive terminal and create a user and db for your
    application: (of course do not use tom and jerry!)

[code]sudo -u postgres psql -d template1

CREATE USER tom WITH PASSWORD ‘myPassword’;
CREATE DATABASE jerry;
GRANT ALL PRIVILEGES ON DATABASE jerry to tom;

– this should show your db now exists
SELECT datname FROM pg_database; [/code]

  1. now we need to load the postgresql extension

[code]cd /etc/php53/conf.d
sudo touch postgre.ini
sudo vi postgre.ini

click ‘i’ for insert and type the following:

extension=pgsql.so

click ‘:wq’

sudo /etc/init.d/httpd2 restart[/code]

  1. create a php file with the following in it. look at it with your
    browser. if it says ‘1’ then you are ready to go! you can now use your previously created db and user in your app. ^^

[php]<?php
echo extension_loaded(‘pgsql’);
?>[/php]

#2

Once you’ve followed the above, to start postgres on system startup, edit /etc/rc.local to add the following line:

setuid postgres /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data

To start psql (the postgres client) from the command line as user postgres (root access to the database), type:

sudo -u postgres psql -h localhost

Once you’ve created a database and user as shown above, you can access it by typing:

psql -h localhost -U fsl2002 fsl2002

fsl2002 is my username and database name.

Also, be sure to edit /usr/local/pgsql/data/pg_hba.conf to require some form of authentication, like “md5.” Instructions for doing this are available in the Postgres documentation. If you leave authentication set to “trust,” all users on your local machine will have access to all databases with all users, without using a password! This is a problem because someone could login as user postgres, the root user, with no password, and then change everyone else’s privileges, delete all databases, etc. Good luck!

#3

Hello.

Thanks for posting this.

It would be really beneficial for everyone if you included this in the Dreamhost Wiki as most people turn to it as source for information on more advanced tasks such as custom php.ini and hopefully installation of postgresql on a VPS.

-Ryan

#4

Big thank you for posting this. Any ideas how to add the PDO driver?

#5

one of these adds the pdo driver
extension=pgsql.so
extension=pdo.so
extension=pdo_odbc.so
extension=pdo_pgsql.so

#6

Specifically, the last one (“extension=pdo_pgsql.so”) loads the PDO driver you’re looking for. pgsql.so is the “classic” (non-PDO) pg driver, pdo.so is built in, and pdo_odbc.so is for talking to other weird databases like DB2 and MSSQL.

#7

and fast forward a few years later, and no more SUDO for VPS users, so the above ‘solution’ is no longer valid.

I have a BUNCH of stuff using postgres I was going to migrate to my new VPS this weekend, and now I discover a VPS isn’t really a VPS. sigh.

#8

DreamHost VPS is a managed service: you don’t get sudo but neither you get to worry about security updates and upgrades.

If you want an unmanaged server, look at DreamCompute: it offers lots of flexibility that VPS can’t offer.

#9

yeah, sigh, I paid for a year of VPS, thinking I was getting a VM I could do what I want with. I’ve been running my own hardware with root level management for decades. I’m kind of in panic mode as I found out Monday that my server is losing its hosting in a couple weeks and I’ve got a LOT of folks stuff on it, and I can’t afford the sorts of coloc charges the local datacenters charge as I just retired from my day job last week. everything on my server is nonprofit or not-for-profit so there’s no cash flow to pay hosting bills.

#10

I’m quite sure you can use what you paid for credit towards the cloud payments. Just open a ticket or start a chat session. If you’re already familiar with managing your own servers, you’ll find yourself at home… There are a few quirks you’ll have to learn, like the difference between ephemeral instances and those that ‘boot from volume’ but those are simple to pick up… and we’re here to help :slight_smile:

#11