Skip to content

Instantly share code, notes, and snippets.

@netsensei

netsensei/postgresinstall.md

Last active December 19, 2023 09:25
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save netsensei/870139cf33275ded5125faa8d35a06db to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save netsensei/870139cf33275ded5125faa8d35a06db to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. netsensei revised this gist Dec 19, 2023. No changes.
  2. netsensei revised this gist Dec 19, 2023. 1 changed file with 6 additions and 6 deletions.
    12 changes: 6 additions & 6 deletions postgresinstall.md
    View file
    Original file line number Diff line number Diff line change
    @@ -5,16 +5,16 @@
    Add the YUM repo to your host:

    ```bash
    $ sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/F-34-x86_64/pgdg-fedora-repo-latest.noarch.rpm
    $ sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/F-37-x86_64/pgdg-fedora-repo-latest.noarch.rpm
    ```

    Install and start PostgreSQL. Here we go with PostgreSQL 10:
    Install and start PostgreSQL. Here we go with PostgreSQL 15:

    ```bash
    $ sudo dnf install postgresql10-server postgresql10
    $ sudo /usr/pgsql-10/bin/postgresql-10-setup initdb
    $ sudo systemctl start postgresql-10
    $ sudo systemctl enable postgresql-10
    $ sudo dnf install postgresql15-server postgresql15
    $ sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
    $ sudo systemctl start postgresql-15
    $ sudo systemctl enable postgresql-15
    ```

    ## Step 2: Set a password on the postgres user
  3. netsensei revised this gist Jan 26, 2023. 1 changed file with 0 additions and 6 deletions.
    6 changes: 0 additions & 6 deletions postgresinstall.md
    View file
    Original file line number Diff line number Diff line change
    @@ -43,12 +43,6 @@ Next up, we want to allow access from local connections to the PostgreSQL server
    ```bash
    sudo vim /var/lib/pgsql/10/data/pg_hba.conf
    ```
    Alter the local section so it reads:

    ```
    # "local" is for Unix domain socket connections only
    local all all md5
    ```

    Alter the IPv4 section so it reads:

  4. netsensei revised this gist Jan 26, 2023. 1 changed file with 7 additions and 1 deletion.
    8 changes: 7 additions & 1 deletion postgresinstall.md
    View file
    Original file line number Diff line number Diff line change
    @@ -41,7 +41,13 @@ Next up, we want to allow access from local connections to the PostgreSQL server
    `/var/lib/pgsql/11/data/postgresql.conf` file:

    ```bash
    sudo vim /var/lib/pgsql/11/data/postgresql.conf
    sudo vim /var/lib/pgsql/10/data/pg_hba.conf
    ```
    Alter the local section so it reads:

    ```
    # "local" is for Unix domain socket connections only
    local all all md5
    ```

    Alter the IPv4 section so it reads:
  5. netsensei revised this gist May 16, 2022. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions postgresinstall.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,5 @@
    # Installation and initial setup of PostgreSQL for local use

    ## Step 1: Installation of PostgreSQL

    Add the YUM repo to your host:
  6. netsensei renamed this gist May 16, 2022. 1 changed file with 0 additions and 0 deletions.
    0 gistfile1.txt → postgresinstall.md
    View file
    File renamed without changes.
  7. netsensei created this gist May 16, 2022.
    78 changes: 78 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,78 @@
    ## Step 1: Installation of PostgreSQL

    Add the YUM repo to your host:

    ```bash
    $ sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/F-34-x86_64/pgdg-fedora-repo-latest.noarch.rpm
    ```

    Install and start PostgreSQL. Here we go with PostgreSQL 10:

    ```bash
    $ sudo dnf install postgresql10-server postgresql10
    $ sudo /usr/pgsql-10/bin/postgresql-10-setup initdb
    $ sudo systemctl start postgresql-10
    $ sudo systemctl enable postgresql-10
    ```

    ## Step 2: Set a password on the postgres user

    The installation will create a `postgres` UNIX user on your system. Postgres is configured by default to log
    in with system user accounts.The `postgres` user is a superuser for PostgreSQL. The `postgres` user is setup
    without a password in PostgreSQL:

    So, let's change it's password first:

    ```bash
    $ sudo -i -u postgres
    $ psql
    psql (10.21)
    Type "help" for help.

    postgres=# alter user postgres with password 'postgres';
    ALTER ROLE
    ```

    ## Step 3: Allow local access to PostgreSQL

    Next up, we want to allow access from local connections to the PostgreSQL server. We'll edit the
    `/var/lib/pgsql/11/data/postgresql.conf` file:

    ```bash
    sudo vim /var/lib/pgsql/11/data/postgresql.conf
    ```

    Alter the IPv4 section so it reads:

    ```
    # IPv4 local connections:
    host all all 127.0.0.1/32 md5
    host all all localhost md5
    ```

    This allows connections from the loopback device as well as 127.0.0.1. The `md5` modifier allows for authentication
    with PostgreSQL user accounts that aren't tied to a system user.

    ## Step 4: creating users and databases

    ```bash
    $ sudo -i -u postgres
    # e.g. netsensei
    $ createuser --interactive
    $ createdb netsensei -O netsensei -T template0 -l en_US.UTF-8 -E UTF8
    ```

    Let's set privileges

    ```bash
    $ sudo -i -u postgres
    $ psql
    psql (10.21)
    Type "help" for help.

    postgres=# grant all privileges on database netsensei to netsensei ;
    ```