General question about configuration and Postgres.app usability

[Kontributer]

1. Edit vs Show Configuration Files After setting up the server, it seems you can only "show" the configuration files, but if those files are moved, how can one update where Postgres.app points to? Especially if each are no longer hosted in the same directory?

2. Database Icons in GUI If you use some sort of package manager like Homebrew or Linuxbrew, why doesn't the Postgres.app show the installed databases? I see visual icons for databases that have configs in ~/Library/Application Support/Postgers/var-* directory, but not in /var/local/postgres directory. Is this a bug or did I do something wrong? Note: I've just updates Postgres.app to 2.x and it's been a long time since I've used it — I had been using psql directly from terminal, so it's possible I've configured something wrong

3. Direct Me to Q&A I tend to look at GitHub primarily for issue tracking for the application, not for general questions like these. Is there a better place to address such questions (that isn't Twitter)? For instance, is there a Gitter, Slack, IRC, or some other place that is more conducive to Q&A/conversation?

[chrispysoft]

1. You have to create a new server to be able to specify a custom data directory. To do this, make sure the left sidebar is visible and press the "+"-button to add a new entry to the server list. Note that such a "server" contains just a reference to the data files; this means that these files won't be deleted if you remove a server from the list.
2. Postgres.app is intended to be a standalone app. That's the reason you only see the databases which have been created using this app.
3. GitHub is the best place for issue tracking. You might also want to check out the homepage for more information: http://postgresapp.com

Hope this helps 😊

[Kontributer]

@chrispysoft thank you :) Question 1 was more about editing an existing server. I pointed it to a location, but when going to Server Settings, the only option is to show the various configuration files. This is problematic for two reasons:

1. There is no way to edit the location of the specific files. Again, if I move, let's say the pg_hba.conf file to a different location (or change the name), there is no way of updating Postgres.app to know this.
2. The only way to change the data directory is to create an entirely new server

That's a shame that it's entirely standalone. I don't mind it, but if you were to create something from CLI, then I was hoping Postgres.app would turn into a better manager for start/stop purposes. I'm curious, what can be done to a vanilla Postgres server installation to work with Postgres.app?

Also, yes I've been on the website — it was helpful, but nothing beats Q&A from knowledgable supporters ;)

[jakob]

1. PostgreSQL expects to find config files inside the data directory, with specific names. Don't move them to other locations, and don't change their name.
2. I don't think PostgreSQL likes it when data directories are moved. As far as I remember, either PostgreSQL itself or some extension expects the absolute path to stay the same, so odd things can happen if you move it. Moving the data directory to a new location should instead be done via dump/restore.
3. But you can add existing data directories in Postgres.app by creating a new server with an existing data directory. Postgres.app only initializes a new cluster when the selected data directory is empty.
4. By default, Postgres.app always uses the bundled binaries. However, that is only a limitation of the server creation UI. If you check the preferences of Postgres.app (using a command like defaults read com.postgresapp.Postgres2) you'll see that an arbitrary binary path can be specified. If you change the binPath of a server from /Applications/Postgres.app/Contents/Versions/9.6/bin to whatever prefix homebrew uses, you can use a different PostgreSQL installation with Postgres.app

If you manually change the preferences of Postgres.app, make sure to only use the defaults command -- don't edit the plist files directly, since they are cached by prefsd. Also, it's best to only change prefs while Postgres.app is not running.

[jakob]

Regarding point 2 (relocating the data dir), I'm not sure if this really is the case. Maybe it is possible to move the data directory -- a quick search of the data dir didn't show any occurrences of the path (except in log files). In that case you can move the data dir like this:

1. Stop the server
2. Delete server in Postgres.app
3. Move Data dir in Finder
4. Create new server in Postgres.app, selecting the new location of the data dir
5. Start the "new" server

(note: I haven't actually tried this, so proceed with caution)

[expiredemulsion]

@jakob I just shutdown my server, copied a 25GB data dir to a new location, deleted the server, created a new one and selected the new location. Then I launched the new server. Everything seems to run perfect!

[mheft-showoff]

@expiredemulsion @jakob I'm trying to do this now. I'm currently in the process of using cp to copy data_directory/var-10 to an external HD where I already have a data_directory/var-10 folder. The original is ~150GB. I need to do this bc the external HD has much more space and I'm running out on my internal :P Is this a good approach?

[jakob]

@mheft-showoff The safest way to relocate a PostgreSQL data directory is to dump/restore (use pg_dumpall, eg. like explained in https://postgresapp.com/documentation/migrating-data.html)

Just copying the data directory will probably work as long as you do it while the PostgreSQL server is stopped. Alternatively, you can also call pg_start_backup and pg_stop_backup. If you don't do that and copy the data directory while the server is running you might end up with corrupted data.

[mheft-showoff]

Awesome thanks. I did stop the originating db before starting the copy and the destination db has been stopped. I was not aware of the potential for corruption. If it breaks, I'll give the link a try 😄 Thanks so much for the super fast help 😄

[chris]

Just adding another report (and thanks) that this technique described by @jakob (stop server, move data, create new server pointed at new location, start new server) worked for me on macOS, with Postgres v10. I moved the entire "Postgres" folder out of ~/Library/Application Support to an external hard drive.

[chris]

One followup note - I'm finding that a lot of times, when I wake up my Mac, that Postgress app has the database as stopped. I suspect this is because it's not immediately seeing the external hard drive's volume and therefore things the DB isn't there/available. I can press start, and then all is ok, but it's an interesting side effect. I wonder if Postgresapp could add say a 30 second wait and retry if it were to see the volume/directory of the database files is not present?