More on PostgreSQL

(We marked visited pages blue)
  • How to Optimize PostgreSQL Performance with VACUUM, ANALYZE, and REINDEX
  • How to find the size of PostgreSQL databases and tables
  • Postgresql Where Clause
  • Postgresql View
  • Postgresql Vacuum Statement
  • Postgresql Upper Function
  • Postgresql Update Statement
  • Postgresql Unique Constraints
  • Postgresql Union Operator
  • Postgresql Union All Operator
  • Postgresql Truncate Table Statement
  • Postgresql Trunc Function
  • Postgresql Trim Function
  • Postgresql Translate Function
  • Postgresql To_number Function
  • Postgresql To_date Function
  • Postgresql To_char Function
  • Postgresql Sum Function
  • Postgresql Substring Function
  • Postgresql Subqueries
  • Postgresql Strpos Function
  • Postgresql Sqrt Function
  • Postgresql Sign Function
  • Postgresql Setseed Function
  • Postgresql Select Statement
  • Postgresql Select Limit Statement
  • Postgresql Rtrim Function
  • Postgresql Rpad Function
  • Postgresql Round Function
  • Postgresql Replace Function
  • Postgresql Repeat Function
  • Postgresql Rename A User
  • Postgresql Random Function
  • Postgresql Primary Keys
  • Postgresql Power Function
  • Postgresql Position Function
  • Postgresql Order By Clause
  • Postgresql Or Condition
  • Postgresql Now Function
  • Postgresql Not Condition
  • Postgresql Mod Function
  • Postgresql Min Function
  • Postgresql Max Function
  • Postgresql Ltrim Function
  • Postgresql Lpad Function
  • Postgresql Lower Function
  • Postgresql Localtimestamp Function
  • Postgresql Localtime Function
  • Postgresql Literals
  • Postgresql Like Condition
  • Postgresql Length Function
  • Postgresql Joins
  • Postgresql Is Null Condition
  • Postgresql Is Not Null
  • Postgresql Intersect Operator
  • Postgresql Insert Statement
  • Postgresql Initcap Function
  • Postgresql Indexes
  • Postgresql In Condition
  • Postgresql Having Clause
  • Postgresql Group By Clause
  • Postgresql Grant Revoke Privileges
  • Postgresql From Clause
  • Postgresql Floor Function
  • Postgresql Find Users In Postgresql
  • Postgresql Extract Function
  • Postgresql Exp Function
  • Postgresql Exists Condition
  • Postgresql Except Operator
  • Postgresql Drop User Statement
  • Postgresql Drop Table Statement
  • Postgresql Div Function
  • Postgresql Distinct Clause
  • Postgresql Delete Statement
  • Postgresql Declaring Variables
  • Postgresql Date_part Function
  • Postgresql Data Types
  • Postgresql Current_timestamp Function
  • Postgresql Current_time Function
  • Postgresql Current_date Function
  • Postgresql Create User Statement
  • Postgresql Create Table Statement
  • Postgresql Create Table As Statement
  • Postgresql Count Function
  • Postgresql Comparison Operators
  • Postgresql Comments Within Sql
  • Postgresql Combining The And And Or Conditions
  • Postgresql Character_length Function
  • Postgresql Char_length Function
  • Postgresql Change A User Password
  • Postgresql Ceiling Function
  • Postgresql Ceil Function
  • Postgresql Btrim Function
  • Postgresql Between Condition
  • Postgresql Avg Function
  • Postgresql Autovacuum Daemon
  • Postgresql And Condition
  • Postgresql Alter Table Statement
  • Postgresql Aliases
  • Postgresql Age Function
  • Postgresql Abs Function
  • Step by Step PostgreSQL installation on Linux

    In this post we will install the latest version of PostgreSQL using the yum package manager.

    •  SSH into EC2 box and run the following commands 
    yum update  yum install postgresql postgresql-server postgresql-devel postgresql-contrib postgresql-docs
    • Start your DB Service 
    service postgresql initdb
    • Edit the pg_hba.conf file
    • the pg_hba.conf file enables client authentication between the PostgreSQL server and the client application. This file consists of a series of entries , which define a host and its associated permissions (e.g., the database it is allowed to connect to, the authentication method to use, and so on).
    # TYPE  DATABASE        USER            ADDRESS                 METHOD
    # "local" is for Unix domain socket connections only
    local   all             all                                     trust
    # IPv4 local connections:
    host    all             power_user               md5
    host    all             other_user               md5
    host    all             storageLoader               md5
    # IPv6 local connections:
    host    all             all             ::1/128                 md5

    How the pg_hba.conf works

    When PostgreSQL receives a connection request it will check the pg_hba.conf file to verify that the machine from which the application is requesting a connection has rights to connect to the specified database. If the machine requesting access has permission to connect, PostgreSQL will check the conditions that the application must meet in order to successfully authenticate. This affects connections that are initiated locally as well as remotely.

    PostgreSQL will check the authentication method via the pg_hba.conf for every connection request. This check is performed every time a new connection is requested from the PostgreSQL server, so there is no need to re-start PostgreSQL after you add, modify or remove an entry in the pg_hba.conf file.

    When a connection is initialized, PostgreSQL will read through the pg_hba.conf one entry at a time, from the top down. As soon a matching record is found, PostgreSQL will stop searching and allow or reject the connection, based on the found entry. If PostgreSQL does not find a matching entry in the pg_hba.conf file, the connection fails completely.

    Table-level permissions still apply to a database, even if a user has permissions to connect to the database. If you can connect, but cannot select data from a table, you may want to verify that your connected user has permission to use SELECT on that table.

    • Edit the postgresql.conf

    Edit the following lines -

    1. #listen_addresses = 'localhost'  change to  listen_addresses='*'
    2. #port = 5432 change to port = 5432
    • Restart PostgreSQL Server 
    service postgresql start
    • Login and change the password for your posgres user.
    ALTER USER postgres WITH PASSWORD 'mynewpasswd';
    And we are done.

    Leave a comment or a question in the comment area bellow if you fell like

    Be a sport and


    Loading... Please wait

    Subscribe to our Newsletter

    Be a sport and