Amazon linux install postgresql

How To Install and Use PostgreSQL on EC2 – Amazon Linux AMI

Introduction

Relational database management systems are a key component of many web sites and applications. They provide a structured way to store, organize, and access information.

PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL querying language. It is a popular choice for many small and large projects and has the advantage of being standards-compliant and having many advanced features like reliable transactions and concurrency without read locks.

In this guide, we will demonstrate how to install Postgres on CentOS 7 and go over some basic ways to use it.

Installation

CentOS’s default repositories contain Postgres packages, so we can install them without a hassle using the yum package system.

Install the postgresql-server package and the “contrib” package, that adds some additional utilities and functionality:

Accept the prompt, by responding with a y .

Now that our software is installed, we have to perform a few steps before we can use it.

Create a new PostgreSQL database cluster:

By default, PostgreSQL does not allow password authentication. We will change that by editing its host-based authentication (HBA) configuration.

Open the HBA configuration with your favorite text editor. We will use vi:

Find the lines that looks like this, near the bottom of the file:pg_hba.conf excerpt (original)

Then replace “ident” with “md5”, so they look like this:pg_hba.conf excerpt (updated)

Save and exit. PostgreSQL is now configured to allow password authentication.

Now start and enable PostgreSQL:

PostgreSQL is now ready to be used. We can go over how it works and how it may be different from similar database management systems you may have used.

Using PostgreSQL Roles and Databases

By default, Postgres uses a concept called “roles” to aid in authentication and authorization. These are, in some ways, similar to regular Unix-style accounts, but Postgres does not distinguish between users and groups and instead prefers the more flexible term “role”.

Upon installation Postgres is set up to use “ident” authentication, meaning that it associates Postgres roles with a matching Unix/Linux system account. If a Postgres role exists, it can be signed in by logging into the associated Linux system account.

The installation procedure created a user account called postgres that is associated with the default Postgres role. In order to use Postgres, we’ll need to log into that account. You can do that by typing:

You will be asked for your normal user password and then will be given a shell prompt for the postgres user.

You can get a Postgres prompt immediately by typing:

You will be auto-logged in and will be able to interact with the database management system right away.

However, we’re going to explain a little bit about how to use other roles and databases so that you have some flexibility as to which user and database you wish to work with.

Exit out of the PostgreSQL prompt by typing:

You should now be back in the postgres user command prompt.

Create a New Role

From the postgres Linux account, you have the ability to log into the database system. However, we’re also going to demonstrate how to create additional roles. The postgres Linux account, being associated with the Postgres administrative role, has access to some utilities to create users and databases.

We can create a new role by typing:

This basically is an interactive shell script that calls the correct Postgres commands to create a user to your specifications. It will only ask you two questions: the name of the role and whether it should be a superuser. You can get more control by passing some additional flags. Check out the options by looking at the man page:

Create a New Database

The way that Postgres is set up by default (authenticating roles that are requested by matching system accounts) also comes with the assumption that a matching database will exist for the role to connect to.

So if I have a user called test1 , that role will attempt to connect to a database called test1 by default.

You can create the appropriate database by simply calling this command as the postgres user:

Connect to Postgres with the New User

Let’s assume that you have a Linux system account called test1 (you can create one by typing: sudo adduser test1 ), and that you have created a Postgres role and database also called test1 .

You can change to the Linux system account by typing:

You can then connect to the test1 database as the test1 Postgres role by typing:

This will log in automatically assuming that all of the components have been configured.

If you want your user to connect to a different database, you can do so by specifying the database like this (make sure you \q to the command prompt):

You can get information about the Postgres user you’re logged in as and the database you’re currently connected to by typing:

This can help remind you of your current settings if you are connecting to non-default databases or with non-default users.

Create and Delete Tables

Now that you know how to connect to the PostgreSQL database system, we will start to go over how to complete some basic tasks.

First, let’s create a table to store some data. Let’s create a table that describes playground equipment.

The basic syntax for this command is something like this:

As you can see, we give the table a name, and then define the columns that we want, as well as the column type and the max length of the field data. We can also optionally add table constraints for each column.

For our purposes, we’re going to create a simple table like this:

We have made a playground table that inventories the equipment that we have. This starts with an equipment ID, which is of the serial type. This data type is an auto-incrementing integer. We have given this column the constraint of primary key which means that the values must be unique and not null.

For two of our columns, we have not given a field length. This is because some column types don’t require a set length because the length is implied by the type.

We then give columns for the equipment type and color, each of which cannot be empty. We then create a location column and create a constraint that requires the value to be one of eight possible values. The last column is a date column that records the date that we installed the equipment.

We can see our new table by typing this:

As you can see, we have our playground table, but we also have something called playground_equip_id_seq that is of the type sequence . This is a representation of the “serial” type we gave our equip_id column. This keeps track of the next number in the sequence.

If you want to see just the table, you can type:

Add, Query, and Delete Data in a Table

Now that we have a table created, we can insert some data into it.

Let’s add a slide and a swing. We do this by calling the table we’re wanting to add to, naming the columns and then providing data for each column. Our slide and swing could be added like this:

You should notice a few things. First, keep in mind that the column names should not be quoted, but the column values that you’re entering do need quotes.

Another thing to keep in mind is that we do not enter a value for the equip_id column. This is because this is auto-generated whenever a new row in the table is created.

We can then get back the information we’ve added by typing:

Here, you can see that our equip_id has been filled in successfully and that all of our other data has been organized correctly.

If our slide breaks and we remove it from the playground, we can also remove the row from our table by typing:

If we query our table again, we will see our slide is no longer a part of the table:

How To Add and Delete Columns from a Table

If we want to modify a table after it has been created to add an additional column, we can do that easily.

We can add a column to show the last maintenance visit for each piece of equipment by typing:

If you view your table information again, you will see the new column has been added (but no data has been entered):

We can delete a column just as easily. If we find that our work crew uses a separate tool to keep track of maintenance history, we can get rid of the column here by typing:

How To Update Data in a Table

We know how to add records to a table and how to delete them, but we haven’t covered how to modify existing entries yet.

You can update the values of an existing entry by querying for the record you want and setting the column to the value you wish to use. We can query for the “swing” record (this will match everyswing in our table) and change its color to “red”. This could be useful if we gave it a paint job:

We can verify that the operation was successful by querying our data again:

As you can see, our slide is now registered as being red.

Conclusion

You are now set up with PostgreSQL on your CentOS 7 server. However, there is still much more to learn with Postgres. Although many of them were written with Ubuntu in mind, these tutorials should be helpful in learning more about PostgreSQL:

Источник

dstroot / install_postgresql.sh

#! /bin/bash

# ##############################################

# To use:

# https://raw.github.com/gist/2776351/.

# chmod 777 install_postgresql.sh

# ./install_postgresql.sh

# ##############################################

echo » ***************************************** «

echo » Installing PostgreSQL «

echo » ***************************************** «

sudo yum -y install postgresql postgresql-server postgresql-devel postgresql-contrib postgresql-docs

sudo service postgresql initdb

# Use MD5 Authentication

sudo sed -i.bak -e ‘ s/ident$/md5/ ‘ -e ‘ s/peer$/md5/ ‘ /var/lib/pgsql9/data/pg_hba.conf

# start

sudo /sbin/chkconfig —levels 235 postgresql on

sudo service postgresql start

# http://imperialwicket.com/aws-install-postgresql-on-amazon-linux-quick-and-dirty

This comment has been minimized.

Copy link Quote reply

framp commented Apr 22, 2013

This comment has been minimized.

Copy link Quote reply

ye commented Aug 1, 2013

Is there a default way to login after this install? Or all login credentials must be added afterwards manually for best practices?

This comment has been minimized.

Copy link Quote reply

arturosalgado commented Apr 27, 2015

how to access to it?, it keeps telling me: authentication failed

This comment has been minimized.

Copy link Quote reply

ghost commented Sep 27, 2016

This comment has been minimized.

Copy link Quote reply

alvirtuoso commented Nov 21, 2016 •

how to access to it?, it keeps telling me: authentication failed
To login after install, login with the systems sudo

1. sudo su — postgres
2. Next psql -U postgres to login as the default user postgres. You should see «postgres=#» which tells you that you’re in.

This comment has been minimized.

Copy link Quote reply

francisrod01 commented Mar 11, 2017

This comment has been minimized.

Copy link Quote reply

ccbenavides commented Sep 12, 2017

password authentication failed for user «postgres»

This comment has been minimized.

Copy link Quote reply

Tectract commented Sep 28, 2017

psql: FATAL: database «ec2-user» does not exist

This comment has been minimized.

Copy link Quote reply

jayemiuesesou commented Dec 16, 2017

Just remove the following

because it changes the way postgres will authenticate to the local server. Just skip it for the moment.

Now, you could change the postgres password:

Enter new password:
Enter it again:

Then go back to ec2-user and now change the pg_hba.conf to activate the MD5 Authentication:

Restart the service, so the changes to be applied:
sudo service postgresql start

Finally, you will be able to login using your new password for postgres user:
psql -U postgres

This comment has been minimized.

Copy link Quote reply

worldofprasanna commented Sep 8, 2018

Useful script . And FYI — with psql (PostgreSQL) 9.2.23, the recommended way to initialise the database cluster is sudo postgresql-setup initdb instead of sudo service postgresql initdb

You can’t perform that action at this time.

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.

Источник

Amazon linux install postgresql

I am going to break this into two articles. The first article will use yum (and the default amazon repositories) for the installation, all defaults, and the ec2 instance EBS root device. Some more in-depth installation techniques are in the next post (Install PostgreSQL 9.0 on Amazon Linux). It seems likely to me that you want some hybrid of the two versions, but this one will get you up and running in a few minutes.

The goal is to install a PostgreSQL server on Amazon Linux, and to allow external access to that database server.

Launch an instance

1. Login, navigate to EC2 tab. Use the “Launch Instance” button to get started.
2. Choose “Basic 64-bit Amazon Linux AMI”
3. Generate a single instance, availability zone probably does not matter, and I am going to run with Micro (Micro is likely sufficient for proof of concept, but I would not recommend using it in a production server).
4. Default “Advanced Instance Options” should be fine.
5. Give your instance a meaningful value for the “Name” key. This is a good habit.
6. Create/Use a key pair for which you have the private key.
7. Create a new security group — I like to dedicate security groups to server types. If you already have a ‘PostgreSQL’ server, use that security group. Avoid using the ‘basic’ security group for all of your servers and simply adding firewall rules whenever necessary.

Security group config

• Group name: postgresql
• Group Description: PostgreSQL database server security group
• Inbound Rules: SSH — 22 from 0.0.0.0/0 and PostgreSQL 5432 from 0.0.0.0/0

Launch the instance.

Connect with something like: ssh -i .ssh/myKey.pem ec2-user@ec2-11-11-11-111.compute-1.amazonaws.com

Install and configure the PostgreSQL server

Use yum to install the PostgreSQL utils, libs, and server. Next, initialize a database cluster and make some necessary updates to the pg_hba.conf file.

At the bottom of pg_hba.conf, change: To read:

Note: Do not use these pg_hba.conf settings on a production server.

Now edit the postgresql.conf file to get the server to listen for requests from other locations.

Uncomment the line (line 59 at time of writing) with “listen_addresses = ‘localhost’ … “ and change the value from ‘localhost’ to ‘’. The file should now read:

Start the PostgreSQL server

After making the appropriate updates to pg_hba.conf and postgresql.conf, we can launch the server. If you started the server prior to making the configuration changes, you will need to restart the server before you can connect.

Congratulations, you now have a PostgreSQL server up and running. We just need to set a password for the postgres user, and you will be able to connect to the server from other locations. On the server, continue with:

Final thoughts

This gets you up and running, and externally accessible. If you are experimenting with PostgreSQL, or need to get a proof of concept up immediately, this may suffice. I can not stress enough that this configuration is inappropriate for production use. Some reasons are:

• We might want to be using PostgreSQL 9
• The postgres user has remote access (don’t do this)
• The postgresql server is using the default port — probably not a major concern. While security through obscurity is not a sufficient security implementation by itself, it does not hurt.
• The entire database is accessible to all users remotely (similar to postgres user having remote access)
• The pgdata directory is using the EBS root device, if the instance goes down, the data goes with it (unless you manually back it up). You also can not use that same EBS device on another instance; this makes vertical scaling difficult.
• We are using the ‘trust’ option in pg_hba.conf. As long as you limit it to socket connections, this can be acceptable, but you should really avoid ‘trust’ — if you want to use it, at least limit it to particular users and be sure it is only available for socket connections.
• Directory location/size. Using the default database cluster directory is not a terrible idea, but it should really be on a different device, this allows you to more conveniently scale the size of the database cluster. Generally, it is nice to move the pgdata directory off the EBS root device, and also to a unique directory (I like /pgdata) — it keeps mount points clean.

I will post a more extensive installation (of PostgreSQL 9.0) later this week.

Источник