Difference between revisions of "Talk:PostgreSQL"

From ArchWiki
Jump to: navigation, search
m (Before you start)
m (amend https://wiki.archlinux.org/index.php?title=Talk:PostgreSQL&diff=prev&oldid=414794 please see Help:Discussion)
 
(24 intermediate revisions by 11 users not shown)
Line 1: Line 1:
 
Updated this wiki to make it more clear and up-to-date. -- Evanlec
 
Updated this wiki to make it more clear and up-to-date. -- Evanlec
  
== Problem with installation ==
+
== UTF-8 ==
I tried to install postgresql with the wiki, but i found this problem:
+
pg_ctl: PID file "/var/lib/postgres/data/postmaster.pid" does not exist
+
Is server running?
+
  
I searched the forums and I found out that an instruction was missed to do:
+
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. --[[User:Cabrilo|Cabrilo]] 06:15, 11 January 2010 (EST)
  
$ initdb -D /var/lib/postgres/data/
+
Is step 4 correct? [[User:Elipsion|Elipsion]] 08:15, 13 January 2010 (EST)
  
I Hope this helps for this wiki
+
:Good catch. Obviously it wasn't, datistemplate should've been set to TRUE for that database. Thanks for noticing it, I fixed it. --[[User:Cabrilo|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. --[[User:Nitmd|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.[[User:Tankgrun|Tankgrun]] 10:07, 12 August 2011 (EDT)
  
== UTF-8 ==
+
== Before you start ==
  
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. --[[User:Cabrilo|Cabrilo]] 06:15, 11 January 2010 (EST)
+
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 --[[User:Mloskot|Mloskot]] ([[User talk:Mloskot|talk]]) 20:03, 24 March 2013 (UTC)
  
Is step 4 correct? [[User:Elipsion|Elipsion]] 08:15, 13 January 2010 (EST)
+
:Agreed. I simplified the "Installing PostgreSQL" section and merged the instructions from "Before you start". Hopefully it is clear now. [[User:Infiniteh|Infiniteh]] ([[User talk:Infiniteh|talk]]) 19:58, 28 April 2015 (UTC)
*Good catch. Obviously it wasn't, datistemplate should've been set to TRUE for that database. Thanks for noticing it, I fixed it. --[[User:Cabrilo|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. --[[User:Nitmd|Nitmd]] 11:55, 18 September 2010 (CDT)
+
According to [http://pgsnake.blogspot.nl/2010/07/postgresql-passwords-and-installers.html this blog post] which is referred to [https://wiki.postgresql.org/wiki/FAQ#I.27m_installing_PostgreSQL_on_Windows_or_OS_X_and_don.27t_know_the_password_for_the_postgres_user 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. [[User:Erikwesselius|erikwesselius]] ([[User talk:Erikwesselius|talk]]) 02:05, 12 August 2015 (UTC)
  
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.[[User:Tankgrun|Tankgrun]] 10:07, 12 August 2011 (EDT)
+
== Package in the repo is not fully ready for systemd ==
  
== Better systemd support ==
+
The page talks about PGROOT, but if you follow the instructions it is never set. From [https://bbs.archlinux.org/viewtopic.php?id=149446 this thread] I found that a file should be created at {{ic|/etc/conf.d/postgres}} that contains the following:
  
The wiki is out dated and the package will most certainly not work with systemd without tinkering with the service files. This article needs a re-write. --Newonelectron
+
##
 +
## Parameters to be passed to postgresql
 +
##
 +
## Default data directory location
 +
PGROOT="/var/lib/postgres"
 +
## Passed to initdb if necessary
 +
INITOPTS="--locale en_US.UTF-8"
 +
## Default log file location
 +
PGLOG="/var/log/postgresql.log"
 +
## Additional options to pass via pg_ctl's '-o' option
 +
#PGOPTS=""
  
== Installing PostgreSQL -> reboot not necessary ==
+
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. {{Unsigned|17:53, 21 June 2013‎|Slippery John}}
  
"Reboot the system to automatically create the file tmpfiles.d for /run/postgresql". I believe "systemd-tmpfiles --create" is enough to create /run/postfix without reboot. But I'm not confident enough to change it without asking first :).
+
== Problem changing default data directory ==
--[[User:Hiciu|Hiciu]] ([[User talk:Hiciu|talk]]) 13:35, 26 November 2012 (UTC)
+
  
edit: well, I was wrong. "systemd-tmpfiles --create" creates /run/postgresql, but it also (at least for me) breaks sudo so reboot is necessary anyway (sudo, after asking for password, returns "System is booting up."). --[[User:Hiciu|Hiciu]] ([[User talk:Hiciu|talk]]) 13:46, 26 November 2012 (UTC)
+
I followed the instructions to change default data directory, however, when starting the service again, it fails with the following error:
  
edit: correct command is "sudo systemd-tmpfiles --create /usr/lib/tmpfiles.d/postgresql.conf" --[[User:Hiciu|Hiciu]] ([[User talk:Hiciu|talk]]) 14:01, 26 November 2012 (UTC)
+
/var/lib/postgres/data" is missing or empty.
  
== Before you start ==
+
What could I be doing wrong? {{Unsigned|22:50, 15 September 2014‎|Ajendrex}}
  
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 --[[User:Mloskot|Mloskot]] ([[User talk:Mloskot|talk]]) 20:03, 24 March 2013 (UTC)
+
== Setting up HHVM ==
 +
 
 +
There are AUR packages {{AUR|hhvm-pgsql}} and {{AUR|hhvm-pgsql-git}}. 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. [[User:Infiniteh|Infiniteh]] ([[User talk: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 <tt>journalctl -t</tt> instead of <tt>… -u</tt>.
 +
 
 +
=== Logging of queries / statements ===
 +
 
 +
# Configure <tt>log_statement</tt> in <tt>/var/lib/postgres/data/postgresql.conf</tt>.
 +
# Tail and follow the log via <tt>journalctl -t postgres -f</tt>.
 +
 
 +
== Simplification ==
 +
 
 +
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[https://bbs.archlinux.org/viewtopic.php?id=149446] 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
 +
su - postgres
 +
initdb --locale en_US.UTF-8 -E UTF8 -D '/var/lib/postgres/data'
 +
 
 +
# enable and start postgresql.service
 +
# 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)
 +
 
 +
[[User:Ts|Ts]] ([[User talk:Ts|talk]]) 12:59, 9 January 2016 (UTC)

Latest revision as of 03:25, 10 January 2016

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

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)

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)

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)

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
PGROOT="/var/lib/postgres"
## Passed to initdb if necessary
INITOPTS="--locale en_US.UTF-8"
## Default log file location
PGLOG="/var/log/postgresql.log"
## Additional options to pass via pg_ctl's '-o' option
#PGOPTS=""

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

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

Simplification

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)