Difference between revisions of "Talk:PostgreSQL"

From ArchWiki
Jump to navigation Jump to search
(Tips and tricks)
m (Logging of queries / statements: mention possibilities for OP)
Line 61: Line 61:
::Strangely, OP specifically mentionned using <tt>-t</tt> instead of <tt>-u</tt>. [[User:Archange|Archange]] ([[User talk:Archange|talk]]) 10:25, 5 June 2018 (UTC)
::Strangely, OP specifically mentionned using <tt>-t</tt> instead of <tt>-u</tt>. [[User:Archange|Archange]] ([[User talk:Archange|talk]]) 10:25, 5 June 2018 (UTC)
:::May be in January 2016 postgresql used syslog method for logging (instead of stderr) or may be OP was trying -u postgres (instead -u postgresql) and it may not have worked.
:::[[User:Amish|Amish]] ([[User talk:Amish|talk]]) 10:32, 5 June 2018 (UTC)
== <s>Simplification</s> ==
== <s>Simplification</s> ==

Revision as of 10:32, 5 June 2018

Updated this wiki to make it more clear and up-to-date. -- Evanlec


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)

Is step 4 correct? Elipsion 08:15, 13 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)
It mighty be necessary, initdb retrieves it from the environment by default, which might not be what you want. Even the locale might be subject to debate. Anyway, it should be your role, or the one of software you install that will use PGSQL to set the appropriate locale/encoding. Archange (talk) 09:27, 5 June 2018 (UTC)

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)

According to this blog post which is referred to on the postgres wiki it is more secure NOT to assign a password to the linux user postgres that is automatically created during postgresql install. erikwesselius (talk) 02:05, 12 August 2015 (UTC)

This has been reworked, and we do not advocate anymore to set a password. Archange (talk) 08:49, 5 June 2018 (UTC)

Package in the repo is not fully ready for systemd

The page talks about PGROOT, but if you follow the instructions it is never set. From this thread I found that a file should be created at /etc/conf.d/postgres that contains the following:

## Parameters to be passed to postgresql
## Default data directory location
## Passed to initdb if necessary
INITOPTS="--locale en_US.UTF-8"
## Default log file location
## Additional options to pass via pg_ctl's '-o' option

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

PGROOT has been set in the service file for a while now. And might be replaced with the standard PGDATA in the future. Archange (talk) 08:51, 5 June 2018 (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

  1. Configure log_statement in /var/lib/postgres/data/postgresql.conf.
  2. Tail and follow the log via journalctl -t postgres -f.

—This unsigned comment is by Blueyed (talk) 14:41, 7 January 2016 (UTC). Please sign your posts with ~~~~!

-t is for backward compatibility with syslog. Proper systemd way is to use -u postgresql
Amish (talk) 10:12, 5 June 2018 (UTC)
Strangely, OP specifically mentionned using -t instead of -u. Archange (talk) 10:25, 5 June 2018 (UTC)
May be in January 2016 postgresql used syslog method for logging (instead of stderr) or may be OP was trying -u postgres (instead -u postgresql) and it may not have worked.
Amish (talk) 10:32, 5 June 2018 (UTC)


Hi. I never edit any wiki page nor talk page. Hopefully I don’t mess with anything. :)

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

  1. install postgres

sudo pacman -S --noconfirm postgres

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

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

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

  1. check if /var/lib/postgres is owned by postgres:postgres
  2. note: probably only /var/lib/postgres/data needs to be owned by postgres:postgres

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

  1. before postgresql can function correctly, the database cluster must be initialized
  2. note: probably `-E UTF8` can be removed altogether, because it's also set in locale (???)
  3. note: the `intdb` command output must end with 'Success.' and some commands how you can start a db server

su - postgres initdb --locale en_US.UTF-8 -E UTF8 -D '/var/lib/postgres/data'

  1. enable and start postgresql.service
  2. note: probably the second command is unnecessary, but a good programmer does not leave anything to chance :)

sudo systemctl enable postgresql.service sudo systemctl start postgresql.service

(the rest of the PostgreSQL page as follows)

Ts (talk) 12:59, 9 January 2016 (UTC)

First 5 steps are just what installing gives you today. Regarding the locale to use, this should be checked by you depending on the intended use. And depending on it, specifying UTF8 might be useful. enabling and starting are both needed, but you can use `start --now`. Archange (talk) 09:29, 5 June 2018 (UTC)

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?

-- Lahwaacz (talk) 16:02, 23 November 2017 (UTC)

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.
I do not disagree with you but I would also say its not necessary.
-- Amish (talk) 17:29, 23 November 2017 (UTC)
It's not necessary for running postgresql.service itself, but the postgres user is used for maintenance as well. Other system users with / as home directory are never used for interactive shell so they also specify either /usr/bin/nologin or /bin/false as the default shell. -- Lahwaacz (talk) 18:07, 23 November 2017 (UTC)
My point is that if things are working without permission then why give permission? Not giving permission does not break or disturb any maintenance. And psql can be run as psql -U postgres from own user account if one wants history.
-- Amish (talk) 00:53, 24 November 2017 (UTC)
Currently there is no point in changing permissions for /var/lib/postgres/ indeed, since only the subfolder data is used. This is because the default upstream configuration is to have this {{ic|data} folder where postgresql is installed (i.e. `/usr/local/postgresql/` by default), in which case you want to have your DB as a subdir and no rights for postgres on the folder above, which contains the binary in particular. If we ever remove that uneeded level (and thus use /var/lib/postgres/ for DBs), this would obviously change. Archange (talk) 08:58, 5 June 2018 (UTC)

IgnorePkg placement

Given that your cluster will *break* if you don't set IgnorePkg up correctly, I think it's an important part of the installation procedure to prepare for this and it should be called out as such. If not by directly putting the IgnorePkg instructions into the installation instructions, then by calling out the importance of reading that section with specific mention of your database breaking if you don't. SirCmpwn (talk) 19:23, 27 November 2017 (UTC)

Moved as a warning to the installation part. Archange (talk) 09:21, 5 June 2018 (UTC)

Note about [postgres]$ needs better placement

Following note under PostgreSQL#Installing_PostgreSQL

Note: Commands that should be run as the postgres user are prefixed by [postgres]$ in this article.

needs a better placement as its applicable to whole article and not just Installing PostgreSQL.

--Amish (talk) 04:56, 1 December 2017 (UTC)

I’ve splitted installation and initial configuration in that purpose. Archange (talk) 09:19, 5 June 2018 (UTC)