Love me some postgres. If I could be so bold as to ask for a feature or two, it would be great if the initial setup were easier to script.
Perhaps this is outdated already but we have to resort to here-documents and other shenanigans to get the initial db and users created. Is there a better way to do this?
Next would be to further improve the clustering, to remove the last reason people continue to use mysql.
Here is my script to create a test instance in about 5 seconds (albeit without any tables define yet, just space for them, with a running server on that space, and an account to create and use the tables in the DB):
(puts tablespace in folder under current directory)
<code>
#!/bin/sh -x
# create and run an empty database
# note: fails miserably if there are spaces in directory names
PGDATA=`pwd`/pgdata
export PGDATA
# make it if not there
mkdir -p $PGDATA
# clean it out if anything there
/bin/rm -rf $PGDATA/*
# set up the database directory layout
initdb
# start the server process
nohup postgres 2>&1 > postgres.log &
sleep 2
# create an empty DB/schema
createdb edrs_test_db
# create a user and password ("demo") to use for connections
psql -d edrs_test_db -c "create user guest password 'guest'"
ps auxww | grep '[p]ostgres'
echo run tail -f postgres.log to monitor database
# vi: nu ai ts=4 sw=4
# * EOF *
</code>
"edrs" is the name of an app - sub in something more applicable. I'm running this on OXS, but should work on Linux as well.
Why `nohup postgres 2>&1 > postgres.log &` instead of `pg_ctl start`? You could also use `pg_ctl -w start` to wait for proper server startup instead of `sleep 2`
Why? Ignorance. I'm used to running postmaster from years past (when not starting from an /etc script). Postmaster is now just called postgres, and it works for a disposable test instance setup.
I'll look into that, though, as it sounds like the right thing vs the sleep hack.
I'd tend to think most pg'ers would want to use their everyday pg tools to setup their db's. For most, that's going to be command line tools, which sounds like what you are not having fantastic success using.
If embedding here-documents inside scripts isn't to your liking, perhaps you should either separate out the seed scripts and cat them in, or have your app-server take on more responsibility for seeding. Systems like Liquibase used by Dropwizard are an example of a more formalized way of performing initial seed, an example of something that might be more what you are looking for if shell scripts are proving difficult to operate.
Scripting is clumsy compared to what? Clicking radio buttons and "next" for 5 minutes???
Another alternative is to make an empty (or test setup) database instance in the "PGDATA" directory, and archive that (while the DB is shut down) for redeployment on another server instance. Unlike Oracle, everything is in one place, as ordinary files and subdirectories.
Fair enough on the security settings. That's something I have to squint at in many system, as I don't deal with it much, other than to grant full access to an app level account. A cheat sheet on common use cases would help.
How would you like to do the initial setup? I'm not perfectly happy with how things are, but I don't see a non SQL interface being better. An easier way to start postgres without network, process a file and shut down, would be goid IMO.
Some pieces already exist, like the pg* commands. Perhaps they could be improved to handle the remaining configuration tasks. The config files themselves could probably use a good redesign as well.
What do other databases do? I haven't used others in a while, but at the time of choosing pg I remember it being more fiddly.
> Some pieces already exist, like the pg* commands
I don't think those really help? You need to start a server for that and be allowed to connect. If you have that you can just as well feed a file to psql to do all the setup at once.
> The config files themselves could probably use a good redesign as well.
Hm. I've dealt with postgresql.conf files for a decade now, so maybe I just don't see the problem with the format itself. I think we should make more parameters auto-tuned, but that's something different to the file format itself.
If you're talking about pg_hba.conf: Wholewheartedly agreed. That's the one thing I remember being terminally confused about back when I started using postgres.
There's createdb from memory (?), but for other bits and pieces, it might be worth spending a bit of time on a thin wrapper script e.g. "create-pg-user foo" that just wraps the annoying parts, but using your environment specific bits and pieces if needed, or using a C/Python/Ruby/Java/etc api. At a bit of a different level maybe some Ansible playbooks that hide away the psql level stuff, so they can be reused.
I fully agree though and in thinking about it, a range of CLI level tools would be nice. I guess then the team would have to play catch up with any syntax/api changes for those tools too, so its adding that annoying extra bit (albeit fairly small probably). But I guess its the annoying extra bit getting down in one place, and not hand-crafted by everyone.
> Perhaps this is outdated already but we have to resort to here-documents and other shenanigans to get the initial db and users created. Is there a better way to do this?
Yes, and it works for most server software, not just PostgreSQL. Use a configuration management system, like Salt, Puppet, or Chef.
Perhaps this is outdated already but we have to resort to here-documents and other shenanigans to get the initial db and users created. Is there a better way to do this?
Next would be to further improve the clustering, to remove the last reason people continue to use mysql.