Yosemite (OSX 10.0) problems with Postgresql

[Jerry Levan]

I just upgraded one of my Mac computers to Yosemite and am having a
small problem.

1) The upgrade evidently *deletes* directories pg_tblspc, pg_twophase, and pg_stat_tmp from
the postgresql data directory. Google said to recreate the missing directories and indeed
Postgresql then was able to start.

2) SystemStarter is no longer available. Gulp… I am not a very good plist creator.
Is there a fairly generic plist I can edit to specify my locations of the
software bits so I can have postgresql started at boot time?

Thanks,

Jerry

[Michael Paquier]

Post by Jerry Levan
1) The upgrade evidently *deletes* directories pg_tblspc, pg_twophase, and pg_stat_tmp from
the postgresql data directory. Google said to recreate the missing
directories and indeed
Postgresql then was able to start.

That's a good way to corrupt your data if using 2PC and tablespaces. (Be
sure to take a backup before the upgrade).

2) SystemStarter is no longer available. Gulp... I am not a very good plist

Post by Jerry Levan
creator.
Is there a fairly generic plist I can edit to specify my locations of the
software bits so I can have postgresql started at boot time?

That's not surprising, this feature is deprecated in Mavericks. And launchd?

[Jerry Levan]

Post by Jerry Levan
1) The upgrade evidently *deletes* directories pg_tblspc, pg_twophase, and pg_stat_tmp from
the postgresql data directory. Google said to recreate the missing directories and indeed
Postgresql then was able to start.
That's a good way to corrupt your data if using 2PC and tablespaces. (Be sure to take a backup before the upgrade).

It might be the case that the deletion would occur if the directories were empty (As they
are on all of my systems ). Perhaps we will find out in a couple of days ;(

Post by Jerry Levan
2) SystemStarter is no longer available. Gulp… I am not a very good plist creator.
Is there a fairly generic plist I can edit to specify my locations of the
software bits so I can have postgresql started at boot time?
That's not surprising, this feature is deprecated in Mavericks. And launched?

Yeah, launchd is still around, that is why I need a .plist file so I can start
Postgresql at boot time.

Post by Jerry Levan
--
Michael

Jerry

[Raghu Ram]

2) SystemStarter is no longer available. Gulp
 I am not a very good plist
creator.
Is there a fairly generic plist I can edit to specify my locations of the
software bits so I can have postgresql started at boot time?

## Enable PostgreSQL to auto start in Mac OS X 10.7.x (Lion)
sudo launchctl load -w
/Library/LaunchDaemons/com.edb.launchd.postgresql-9.1.plist

NOTE: "com.edb.launchd.postgresql-9.1.plist" file contains PostgreSQL
Data directory and PostgreSQL binary locations.


## Manually Start PostgreSQL
## su as user "postgres" and run server [sudo su - postgres]:
cd /Library/PostgreSQL/9.1/bin/
./pg_ctl -D /Library/PostgreSQL/9.1/data/ start

## Manually Stop PostgreSQL
./pg_ctl -D /Library/PostgreSQL/9.1/data/ stop

Thanks & Regards

Raghu Ram

[Israel Brewster]

Post by Jerry Levan
2) SystemStarter is no longer available. Gulp… I am not a very good plist creator.
Is there a fairly generic plist I can edit to specify my locations of the
software bits so I can have postgresql started at boot time?

I'm not familiar with SystemStarter, but here is the plist file that I've been using on several of my machines to start my own copy of postgres. Modified from the copy that comes with apple's server.app. This file goes in /Library/LaunchDaemons, and then can be loaded with the command

sudo launchctl load -w /Library/LaunchDaemons/<whatever you named the file.plist>

substitute load with unload to stop postgres.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
<key>Label</key>
<string>org.postgresql.postgres-mine</string>
<key>ProgramArguments</key>
<array>
<string>/usr/local/pgsql/bin/postmaster</string>
<string>-D</string>
<string>/usr/local/pgsql/data</string>
<string>-h</string>
<string>*</string>
</array>
<key>RunAtLoad</key>
<true/>
<key>UserName</key>
<string>_postgres</string>
</dict>
</plist>

-----------------------------------------------
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
[phone]
-----------------------------------------------

Post by Jerry Levan
Thanks,
Jerry
--
http://www.postgresql.org/mailpref/pgsql-general

[Tom Lane]

Post by Israel Brewster

2) SystemStarter is no longer available. Gulp… I am not a very good plist creator.
Is there a fairly generic plist I can edit to specify my locations of the
software bits so I can have postgresql started at boot time?

I'm not familiar with SystemStarter, but here is the plist file that
I've been using on several of my machines to start my own copy of
postgres. Modified from the copy that comes with apple's server.app.

FWIW, I ran into the no-more-SystemStarter problem over the weekend on a
machine that I run a secondary DNS server on. It turns out that Apple's
recipe for this sort of thing omits a very important consideration:
during boot, launchd will happily launch the specified program before
any networking has come up. If you check after rebooting, you'll probably
find that your postmaster has started but has only bound a socket to
127.0.0.1, because no external TCP interfaces were alive when it looked.
Maybe this is OK for your usage of Postgres, but I was definitely not
happy about the machine not offering any DNS service externally :-(

After some googling I ended up with the following pretty hacky solution:
interpose a shell script that waits for networking to be up.

$ more /Library/LaunchDaemons/mydns.plist
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
<key>Label</key>
<string>mydns</string>
<key>ProgramArguments</key>
<array>
<string>/bin/sh</string>
<string>/usr/local/sbin/mydnsctl.sh</string>
</array>
<key>KeepAlive</key>
<true/>
</dict>
</plist>

$ more /usr/local/sbin/mydnsctl.sh
#!/bin/sh

# fetch the Apple-provided network check function
# also sets a sane PATH
. /etc/rc.common

CheckForNetwork
while [ x"$NETWORKUP" != x"-YES-" ]
do
sleep 1
NETWORKUP=""
CheckForNetwork
done

# use -f flag because launchd doesn't want daemons to fork
exec /usr/local/sbin/named -f


After looking at CheckForNetwork() I don't have a lot of confidence
that it would work if you have multiple network interfaces, but at
least for the bog standard one-NIC situation this seems to work.

The recipe we have in contrib/start-scripts/osx/ has been obsolete
for awhile, but it's well and truly broken for Yosemite. Anyone care
to submit a patch to replace that example with something launchd-based?

regards, tom lane

[Craig Ringer]

This post might be inappropriate. Click to display it.

[Wells Oliver]

Not to derail, but I've found this distribution of PG on OSX to be
fantastic:

http://postgresapp.com/

It includes all the proper launch files that work with Yosemite.

Post by Craig Ringer

Post by Jerry Levan
I just upgraded one of my Mac computers to Yosemite and am having a
small problem.

Are you talking about the PostgreSQL built-in to OS X?
The PostgreSQL Development Group (PGDG) doesn't control Apple's
packaging of PostgreSQL at all. Unfortunately.
It is my understanding that Apple install PostgreSQL for Apple's
internal use in their products, like OS X Server, Server.app, etc.
Apple have finally taken PostgreSQL off the default PATH in their new OS
X release, but it looks like they've done it pretty brutally, pretty
much just converting the databases their products use and discarding the
others.
I don't have a Mac, (and frustratingly, Apple still don't offer VMs for
developer use), so I can't really test or investigate details.
If you're talking about the version of PostgreSQL bundled in OS X, then
I suggest contacting Apple support. Angrily.
Mac-using hackers, anyone know more? I've seen a few reports of people
losing data on Stack Overflow and apple.stackexchange.com and I'm quite
concerned about this.

Post by Jerry Levan
1) The upgrade evidently *deletes* directories pg_tblspc, pg_twophase,

and pg_stat_tmp from

Post by Jerry Levan
the postgresql data directory. Google said to recreate the missing

directories and indeed

Post by Jerry Levan
Postgresql then was able to start.

That's a truly insane thing for an updater to do for any reason. Why
would it do that?
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
http://www.postgresql.org/mailpref/pgsql-general

[Craig Ringer]

Post by Jerry Levan
I just upgraded one of my Mac computers to Yosemite and am having a
small problem.

My preliminary investigation, limited by not having access to a Mac,
suggests that there's a lot more than a "small" problem for people
who're using the built-in PostgreSQL bundled by Apple in prior OS X
releases.

http://blog.2ndquadrant.com/ware-yosemite-possible-postgresql-upgrade-issues-os-x-10-10/

This needs urgent investigation by community members with access to
Apple hardware. Anyone who is still on 10.9, or who can downgrade to
10.9, do some tests, and upgrade to 10.10 again, please get in touch.
Especially if you use Server.app.

[Michael Paquier]

Post by Craig Ringer
This needs urgent investigation by community members with access to
Apple hardware. Anyone who is still on 10.9, or who can downgrade to
10.9, do some tests, and upgrade to 10.10 again, please get in touch.
Especially if you use Server.app.

If there are people using a Mac with VMware Fusion, note that you can
create a VM running OSX by installing it from the recovery partition,
something useful for tests. No need to switch definitely the main machine.

[TigerNassau]

If of any help I will be upgrading
From maverick to yosemite but its only dev machine with small databases
Sent from my LG Mobile

Post by Craig Ringer

Post by Jerry Levan
I just upgraded one of my Mac computers to Yosemite and am having a
small problem.

My preliminary investigation, limited by not having access to a Mac,
suggests that there's a lot more than a "small" problem for people
who're using the built-in PostgreSQL bundled by Apple in prior OS X
releases.
http://blog.2ndquadrant.com/ware-yosemite-possible-postgresql-upgrade-issues-os-x-10-10/
This needs urgent investigation by community members with access to
Apple hardware. Anyone who is still on 10.9, or who can downgrade to
10.9, do some tests, and upgrade to 10.10 again, please get in touch.
Especially if you use Server.app.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
http://www.postgresql.org/mailpref/pgsql-general

[Craig Ringer]

Post by TigerNassau
If of any help I will be upgrading
From maverick to yosemite but its only dev machine with small databases

Are you using the PostgreSQL bundled by Apple though, or one you
installed yourself?

If you're using the Apple-bundled one, make VERY sure to back everything
up carefully with pg_dump before upgrading. Then tell me how it goes.

If you're using one you installed separately, it'd be handy if you could
create a database with a few trivial tables in the Apple-provided
PostgreSQL before the upgrade, then see if you can find any trace of
them after the upgrade. I believe that the Apple-provided one only
starts up by default if you've installed Server.app, but I'm not certain
of that and - again - can't easily verify it.

[John R Pierce]

Post by Craig Ringer
If you're using the Apple-bundled one, make VERY sure to back everything
up carefully with pg_dump before upgrading. Then tell me how it goes.

I'd recommend doing..

$ pg_dumpall --globals-only -f 20141021_globals.sql
$ for db in mydb1 mydb2 ... ; do pg_dump -Fc -d $db -f
20141012_dump.$db.pgdump ; done

this will give you the maximum flexibility when you restore.
--
john r pierce 37N 122W
somewhere on the middle of the left coast