:While your points are valid but then in normal case only data directory is accessed by postgres. So why give additional permission? psql can be run as normal user instead of postgres user if psql_history is required at all.

+

+

:Also many other system users have / as home directory which is also owned by root. So its not compulsory that home directory must be write-able.

UTF-8

Added instructions on how to create new databases as unicode by default. This seems relevant as the default "encoding" SQL_ASCII is pretty useless for most people. --Cabrilo 06:15, 11 January 2010 (EST)

Good catch. Obviously it wasn't, datistemplate should've been set to TRUE for that database. Thanks for noticing it, I fixed it. --Cabrilo 12:00, 13 January 2010 (EST)

Is this section still necessary? I am just learning, but did a "show client_encoding" on template1 and it returns UTF8 out of the box. --Nitmd 11:55, 18 September 2010 (CDT)

I think it's still necessary (and thank you for making this section). Just removed "vacuum freeze", since it's no longer recommended and the command will be obsolete in future releases of postgresql. BTW: I ran into a problem with the statement to disallow connections to the template1 database. This is because I wanted to build a new database named "postgres". After dropping the postgresql db, creating a new template1 and disallowing the connections to template1, I had no database to connect to, so I couldn't create a new database. Had to delete the data dir and start over.Tankgrun 10:07, 12 August 2011 (EDT)

Before you start

This section is confusing because it assumes "postgres" user has already been created, whereas it is created by "pacman -S postgresql" issued in the following section. I'd suggest to restructure the article this way: 1. Installing PostgreSQL - with single package installation instruction only, 2. Before you start, 3. Configuring PostgreSQL --Mloskot (talk) 20:03, 24 March 2013 (UTC)

Agreed. I simplified the "Installing PostgreSQL" section and merged the instructions from "Before you start". Hopefully it is clear now. Infiniteh (talk) 19:58, 28 April 2015 (UTC)

Maybe this could go into the postgresql.conf, I dunno. All I know is that systemd will refuse to run the service if PGROOT isn't set. I'll give putting that in the conf file a try and update with results. —This unsigned comment is by Slippery John (talk) 17:53, 21 June 2013‎. Please sign your posts with ~~~~!

Problem changing default data directory

I followed the instructions to change default data directory, however, when starting the service again, it fails with the following error:

/var/lib/postgres/data" is missing or empty.

What could I be doing wrong? —This unsigned comment is by Ajendrex (talk) 22:50, 15 September 2014‎. Please sign your posts with ~~~~!

Setting up HHVM

There are AUR packages hhvm-pgsqlAUR and hhvm-pgsql-gitAUR. Could either of these be used instead of the manual instructions that are given? I am not a PostgreSQL user, so I do not want to break something inadvertently by making such a change myself. Infiniteh (talk) 18:58, 28 April 2015 (UTC)

Tips and tricks

I don't know where to put this on the main page, but found it useful / missing
- especially the part about using journalctl -t instead of … -u.

Logging of queries / statements

Configure log_statement in /var/lib/postgres/data/postgresql.conf.

Tail and follow the log via journalctl -t postgres -f.

Simplification

I’d like to suggest that the section of PostgreSQL dealing with starting a new database should be simplified. I’m just a psql newbie and I have found the instructions quite a mess – and I wouldn’t even solve my issues unless I’ve found this[1] page. In my opinion, the PostgreSQL (and other Arch Wiki pages) should be explicit about what commands are to executed, though the links contaning more information about the commands and their options are great. Here is my list of commands how to create a psql db (probably some corrections are needed):

install postgres

sudo pacman -S --noconfirm postgres

create new db user (-s stand for shell; -m creates /home/$USER; -U adds a group with the same name as $USER; postgres is the user's name)

useradd -s /bin/bash -m -U postgres

check if /var/lib/postgres/data dir was created

sudo mkdir -p /var/lib/postgres/data

check if /var/lib/postgres is owned by postgres:postgres

note: probably only /var/lib/postgres/data needs to be owned by postgres:postgres

sudo chown -R postgres:postgres /var/lib/postgres

before postgresql can function correctly, the database cluster must be initialized

note: probably `-E UTF8` can be removed altogether, because it's also set in locale (???)

note: the `intdb` command output must end with 'Success.' and some commands how you can start a db server

From https://www.postgresql.org/support/versioning/ : "Beginning with version 10, a major release is indicated by increasing the first part of the version, e.g. 10 to 11. Before version 10, a major release was indicated by increasing either the first or second part of the version number, e.g. 9.5 to 9.6."

Why is /var/lib/postgres/ owned by root?

Having a UNIX user which does not have permissions to write into its own home directory is somewhat perverse. It causes multiple problems, e.g. [2], psql run as postgres cannot write to ~/.psql_history and so on.

Even the official documentation says that it is recommended to have /usr/local/pgsql (their equivalent of /var/lib/postgres) to be owned by the postgres user.

Does anybody disagree or shall I create a bug report to change it in the Arch package?

While your points are valid but then in normal case only data directory is accessed by postgres. So why give additional permission? psql can be run as normal user instead of postgres user if psql_history is required at all.

Also many other system users have / as home directory which is also owned by root. So its not compulsory that home directory must be write-able.