Thread: Yosemite (OSX 10.0) problems with Postgresql

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

On Oct 20, 2014, at 7:45 AM, Michael Paquier <michael.paquier@gmail.com> wrote:

>
>
> On Mon, Oct 20, 2014 at 9:26 PM, Jerry Levan <jerry.levan@gmail.com> wrote:
> 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 ;(
>
> 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.

> --
> Michael

Jerry

On Oct 20, 2014, at 4:26 AM, Jerry Levan <jerry.levan@gmail.com> wrote:
> 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
myown 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
(907) 450-7293
-----------------------------------------------


>
> Thanks,
>
> Jerry
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

On 10/20/2014 08:26 PM, Jerry Levan wrote:
> 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.

> 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 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

Israel Brewster <israel@ravnalaska.net> writes:
> On Oct 20, 2014, at 4:26 AM, Jerry Levan <jerry.levan@gmail.com> wrote:
>> 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

On 10/20/2014 08:26 PM, Jerry Levan wrote:
> 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

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

Craig Ringer <craig@2ndquadrant.com> wrote:

>On 10/20/2014 08:26 PM, Jerry Levan wrote:
>> 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
>
>
>--
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general

On 10/21/2014 10:49 PM, TigerNassau wrote:
> 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.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On 10/21/2014 5:14 PM, Craig Ringer wrote:
> 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