PostgreSQL 9.3 setup after the initial Macports installation

I recently installed PostgreSQL 9.3 using MacPorts. I know some of you use HomeBrew but I still have enough things installed using MacPorts, that work and upgrade well, that I'm still in the MacPorts camp. MacPorts installed PostgreSQL OK, but getting the installation initialized and running took a few hours on a Saturday. Here is a shell script to summarize what finally worked for me.

In creating the script above, I started with hints from:
https://coderwall.com/p/xezzaa

I also needed these hints on OS X postgres user creation:
http://www.working-software.com/node/30

sudo dscl . change /Users/postgres NFSHomeDirectory /opt/local/var/db/postgresql82 /opt/local/var/db/postgresql93

This did not work:

sudo dscl . change /Users/postgres RealName "\n PostgreSQL-82 Server" 'PostgreSQL-93 Server'
  <main> attribute status: eDSAttributeNotFound
  <dscl_cmd> DS Error: -14134 (eDSAttributeNotFound)

But this did work:

sudo dscl . -create /Users/postgres RealName "PostgreSQL Administrator"

See: http://gilmation.com/articles/compile-and-configure-postgresql-on-mac-os-x/

Pain after macports "routine" MySQL/PHP update, now relieved

I did a port selfupdate, and updated a few ports, then spent four hours underwater dealing with things that suddenly stopped working:

  php5 fatal error call to undefined function mysql_connect

Making sure I had the following installed (with options I needed) fixed the error above.

• mysql5-server 5.1.42_0 (was already OK)
• php5 5.3.1_1+apache2+darwin_10+fastcgi+macosx+mysql5+postgresql83+sqlite
• php5-mysql 5.3.1_2+mysqlnd

I checked the setup with php_info(), but then I got another error:

  mysql_connect() [function.mysql-connect]: [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock)

After some investigation, I created a php.ini file:

$ sudo cp /opt/local/etc/php5/php.ini{-development,}
  $ sudo chown username /opt/local/etc/php5/php.ini

Edited php.ini:

< mysql.default_socket =
  --
  > mysql.default_socket = /opt/local/var/run/mysql5/mysqld.sock

(I have a memory of having done something like this before, but apparently I had to do it again.)

Then, of course, I got new errors:

mysql_connect() [function.mysql-connect]: OK packet 6 bytes shorter than expected.
  mysql_connect() [function.mysql-connect]: mysqlnd cannot connect to MySQL 4.1+ using old authentication in ...

The Google helpfully led me to this stackoverflowanswer (why didn't I think of that first?!), and the answer was a total surprise. Happily, after the final fix, everything is working again:

mysql> select Length(`Password`), Substring(`Password`,1,1)
  from `mysql`.`user` where `user` = 'username';
  +--------------------+---------------------------+
  | Length(`Password`) | Substring(`Password`,1,1) |
  +--------------------+---------------------------+
  |                 16 | 3                         |
  |                 16 | 3                         |
  |                  0 |                           |
  +--------------------+---------------------------+
  3 rows in set (0.07 sec)
  mysql> set password for 'username'@'localhost' = PASSWORD('password');
  Query OK, 0 rows affected (0.00 sec)
  mysql> select Length(`Password`), Substring(`Password`,1,1)
  from `mysql`.`user` where `user` = 'username';
  +--------------------+---------------------------+
  | Length(`Password`) | Substring(`Password`,1,1) |
  +--------------------+---------------------------+
  |                 41 | *                         |
  |                 16 | 3                         |
  |                  0 |                           |
  +--------------------+---------------------------+
  3 rows in set (0.00 sec)

Now that a good chunk of my "free day" has been used up, I think I'm going outside for some fresh air.