#414 Update Postgres guide
Merged 5 months ago by mattdm. Opened 6 months ago by nikodunk.
Unknown source master  into  master

@@ -4,79 +4,80 @@

  == Installation

  

  The installation and initialization of the postgresql server is a little

- bit different in comparison to other packages and other linux distros.

+ bit different in comparison to other packages and other Linux distros.

  This document aims to summarize basic installation steps relevant to

- recent fedora release. In the first place, you may consider installing a newer

- version than is packaged for Fedora, see https://yum.postgresql.org/[1].

- However, this is not recommended.

+ recent Fedora Linux releases. 

  

  ....

- sudo yum install postgresql-server postgresql-contrib

+ sudo dnf install postgresql-server postgresql-contrib

  ....

  

- Or with dnf in Fedora 22 and later versions:

+ The postgresql server is not running and disabled by default. To set it to start at boot, run:

  

  ....

- sudo dnf install postgresql-server postgresql-contrib

+ sudo systemctl enable postgresql

  ....

  

- The postgresql server is turned off and disabled by default. You can

- enable its start during the boot using following command:

+ The database needs to be populated with initial data after installation. The database initialization could be done using following command. It

+ creates the configuration files postgresql.conf and pg_hba.conf

  

  ....

- sudo systemctl enable postgresql

+ sudo postgresql-setup --initdb --unit postgresql

  ....

  

- You can start the postgresql server only when necessary as follows.

+ To start the postgresql server manually, run

  

  ....

- sudo systemctl start postgresql 

- Job for postgresql.service failed. See 'systemctl status postgresql.service' and 'journalctl -xn' for details.

+ sudo systemctl start postgresql 

  ....

  

- The database needs to be populated with initial data after installation.

- The error log describes the problem and its solution.

+ 

+ [[user-creation-and-database-creation]]

+ == User Creation and Database Creation

+ 

+ Now you need to create a user and database for the user. This needs to be run from a `postgres` user account on your system.

  

  ....

- journalctl -xn

- -- Logs begin at Mon 2013-11-04 14:38:33 CET, end at Thu 2013-11-14 11:45:56 CET. --

- Nov 14 11:45:34 mlich-lenovo.usersys.redhat.com sudo[2054]: jmlich : TTY=pts/2 ; PWD=/home/jmlich ; USER=root ; COMMAND=/bin/systemctl status postgresql

- Nov 14 11:45:37 mlich-lenovo.usersys.redhat.com sudo[2073]: jmlich : TTY=pts/2 ; PWD=/home/jmlich ; USER=root ; COMMAND=/bin/systemctl status postgresql

- Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com sudo[2105]: jmlich : TTY=pts/2 ; PWD=/home/jmlich ; USER=root ; COMMAND=/bin/systemctl start postgresql

- Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com systemd[1]: Starting PostgreSQL database server...

- -- Subject: Unit postgresql.service has begun with start-up

- -- Defined-By: systemd

- - Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel

- --

- -- Unit postgresql.service has begun starting up.

- Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com postgresql-check-db-dir[2108]: An old version of the database format was found.

- Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com postgresql-check-db-dir[2108]: Use "postgresql-setup upgrade" to upgrade to version 9.3.

- Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com postgresql-check-db-dir[2108]: See /usr/share/doc/postgresql/README.rpm-dist for more information.

- Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com systemd[1]: postgresql.service: control process exited, code=exited status=1

- Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com systemd[1]: Failed to start PostgreSQL database server.

- -- Subject: Unit postgresql.service has failed

- -- Defined-By: systemd

- -- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel

- -- Documentation: `http://www.freedesktop.org/wiki/Software/systemd/catalog/be02cf6855d2428ba40df7e9d022f03d

- --

- -- Unit postgresql.service has failed.

- --

- -- The result is failed.

- ....

- 

- The database initialization could be done using following command. It

- creates the configuration files postgresql.conf and pg_hba.conf

+ sudo -u postgres psql

+ ....

+ 

+ From here you can create a postgres user and database. Here, we will assume your computer's user account is called `lenny`. Note: you can also run this from the shell as well with `createuser lenny` and `createdb --owner=lenny carl`.

  

  ....

- sudo postgresql-setup initdb

+ postgres=# CREATE USER lenny WITH PASSWORD 'leonard';

+ postgres=# CREATE DATABASE my_project OWNER lenny;

  ....

  

- Or on Fedora 22 and later:

+ It might be good idea to add password for the `postgres` user while you're at it:

  

  ....

- sudo postgresql-setup --initdb --unit postgresql

+ postgres=# \password postgres

+ ....

+ 

+ Press Ctrl + D or `\q` to leave the psql session running as user `postgres`. Now you can access your new database from your user account (`lenny`) and start using it.

+ 

+ ....

+ psql my_project

+ ....

+ 

+ 

+ [[initial configuration]]

+ == Initial Configuration

+ 

+ The postgresql server is using two main configuration files

+ 

+ * /var/lib/pgsql/data/postgresql.conf

+ * /var/lib/pgsql/data/pg_hba.conf

+ 

+ 

+ If you’re getting ident errors from your app you’ll probably need to perform the accepted solution described at https://serverfault.com/questions/406606/postgres-error-message-fatal-ident-authentication-failed-for-user?newreg=a4fdc3e21349449985cc65b82399c5b4 

+ ....

+ sudo gedit /var/lib/pgsql/data/pg_hba.conf

  ....

  

+ and edit `host all all 127.0.0.1/32 ident` to `host all all 127.0.0.1/32 md5`. This should allow most applications to connect with username/password.

+ 

+ 

  [[upgrade]]

  == Upgrade

  
@@ -88,8 +89,7 @@

  Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com postgresql-check-db-dir[2108]: Use "postgresql-setup upgrade" to upgrade to version 9.3.

  ....

  

- With version 9 you can use the upgrade tool. It is packaged as

- `postgresql-upgrade`:

+ With version 9 you can use the upgrade tool. It is packaged as `postgresql-upgrade`:

  

  ....

  postgresql-setup upgrade
@@ -166,54 +166,15 @@

  setsebool -P httpd_can_network_connect_db on

  ....

  

- [[user-creation-and-database-creation]]

- == User Creation and Database Creation

- 

- Eventually, you need to create a user (and database for the user).

- First, you have to switch the user to interact with `postgres`:

- 

- ....

- su - postgres

- ....

- 

- and then run postgre's interactive shell:

- 

- ....

- psql

- psql (9.3.2)

- Type "help" for help.

- 

- postgres=#

- ....

- 

- From there you can run user creation commands:

- 

- ....

- postgres=# CREATE USER lenny WITH PASSWORD 'leonard';

- postgres=# CREATE DATABASE carl OWNER lenny;

- ....

- 

- You can do this from the system shell as well:

- 

- ....

- createuser lenny

- createdb --owner=lenny carl

- ....

- 

- It might be good idea to add password for the `postgres` user:

- 

- ....

- postgres=# \password postgres

- ....

- 

  [[configuration]]

  == Configuration

  

- The postgresql server is using two main configuration files

+ As mentioned above, the postgresql server is using two main configuration files

  

  * /var/lib/pgsql/data/postgresql.conf

  * /var/lib/pgsql/data/pg_hba.conf

  

+ 

  [[systemd]]

  === systemd

  

I am a web developer, and recently switched to Fedora from Mac OS. Postgres setup on Fedora is actually pretty easy once you get the hang of it, but it is very frustrating the first few times due to the current documentation.

I'd like to propose updating the format of this guide - and was directed from Ask Fedora and Join SIG - to reflect current Fedora version, but also make it more readable, in chronological order, and less confusing. I have struggled with this document every time I have googled it, as described here: https://ask.fedoraproject.org/t/how-to-set-up-postgres-in-fedora-35/19442.

I am striving to improve clarity without reducing information. Most of these edits are simply re-ordering existing commands to reflect the order a developer will actually have to perform them in to get PSQL up and running on Fedora, though I did also remove some old yum commands that were strewn in and relevant to versions older than Fedora 22.

I have already re-directed the old Postgres Wiki article to the new postgres quick docs. Now IMO the quick docs need to be updated.

1 new commit added

  • Update modules/ROOT/pages/postgresql.adoc
6 months ago

for a new user/developer, having old yum directions is supremely confusing.

pretty please pagure-ci rebuild

6 months ago

1 new commit added

  • Update modules/ROOT/pages/postgresql.adoc
6 months ago

1 new commit added

  • Update modules/ROOT/pages/postgresql.adoc
6 months ago

What about replacing this (and the following bit) with

sudo -u postgres psql

?

Could replace this with sudo systemctl enable --now postgresql and remove the separate enable line above.

If you think having them separate is better, that's fine. :)

"Fedora releases" or "Fedora Linux releases", please.

Capitalize Linux while we're making changes?

Above 5 lines not needed if sudo - is used.

1 new commit added

  • Update modules/ROOT/pages/postgresql.adoc
6 months ago

Implemented! Thank you @mattdm - excellent inputs. I didn't think of using the sudo -u postgres psql command, which I only vaguely knew about tbh.

I do however think it's good to have systemctl enabling and starting separate if you don't mind, as:
1. It's useful to have the psql start command separate here - in later use the user will only be starting and not re-enabling for launch at startup.
2. If you immediately start psql without setting up your database first (init-db on the next line), it'll crash until you have run it. This was how the original author (thank you so much for all the hard work!) wrote the guide, and it's a bit of a non-standard user experience (getting lead to an error on purpose).

1 new commit added

  • Update modules/ROOT/pages/postgresql.adoc
6 months ago

1 new commit added

  • Update modules/ROOT/pages/postgresql.adoc
5 months ago

Pull-Request has been merged by mattdm

5 months ago
Metadata