Install and use PostgreSQL 9.3 on Amazon EC2

Question

I'm having a problem with an Amazon Linux (CentOs based) instance when trying to install the latest stable Postgres (version 9.3).

That version doesn't ships in the main repositories of that distro and I tried some of the RPM's that are available in the Postgres download site. CentOs and Fedora versions didn't worked, since they complained about the distro I'm using not to be the one they're made to. The Red Hat .rpm worked well and I could see the new packages available to yum.

I used the following:

wget http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-redhat93-9.3-1.noarch.rpm
rpm -Uvh pgdg-redhat93-9.3-1.noarch.rpm

So, I installed the Postgres packages of the version I'm interested in:

yum install postgresql93 postgresql93-devel postgresql93-server

So far, so good. Everything installed beatifully (just a php package conflicted, but I blew it up).

As said in every blog and help page, I initialized the database. For me, there is a need to change the PORT, so I did it in /etc/init.d/postgresql-9.3 script, changing the port to 5552, that is available in this box. To initialize and start the server, nothing new:

service postgresql-9.3 initdb 
service postgresql-9.3 start

(the name of the service has the version, differently from the service installed from the mainstream amazon repositories)

It just works. I have no sign of problem until I tried to connect using psql utility:

[root@local]# su - postgres
-bash-4.1$ psql 
psql: FATAL:  role "postgres" does not exist

As usual, I also can't do it with root, but with the user "postgres" it seems reasonable that the role at least should have been created.

I can't use createdb for the same strange reason. I've been receiving the same error for any operation I try in the bin folder that could interact with the database. For that, Im stuck with a "working" database but no user able to connect to it.

When searching around the tips I could find are always related to a user trying to connect using the root user and the solution is to use postgres user and I'm doing it, as you saw.

This is a problem that just happened when trying the version 9.3. When I tried the repository package (that is still 9.2) it worked just fine. I could connect and test the database normally.

Before this package I tried to compile Postgres from source, but the hell of dependencies just didn't let me go.

Is there any other adjust I might be missing, some package that is needed or even a guess you could share to help me solve this issue?

It is not clear which of those commands you executed as root and which you execute as ec2-user. And service postgresql-9.3 is not in itself a legal command, it must have had a ` start` after it? — jjanes
– jjanes, Commented Jun 20, 2014 at 16:49
Everything that was not specified to have been run as "postgres" was runned as "root". The service was a typo. Just corrected it. — D. Melo
– D. Melo, Commented Jun 21, 2014 at 18:13
Since you changed the port of the server from the default, you need to specify that non-default port when you execute psql. (The error message you get suggests that some instance of PostgreSQL is running on the default port, just not the instance you want--but how did it get there?) — jjanes
– jjanes, Commented Jun 21, 2014 at 22:56

facundofarias · Accepted Answer · 2015-02-23 12:34:59Z

4

I had the same issue than you, after installing Postgresql on centOS by using yum. The thing is that Postgresql comes with a built-in user called postgres, which you should use to perform the first login. So, just try with:

su - postgres
psql

An then, you can create databases, users, and so on. Hope this helps!

answered Feb 23, 2015 at 12:34

facundofarias

3,04933 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Natan Rubinstein Over a year ago

I would add that before I needed to put sudo -i This is my console: [ec2-user@ip-172-30-0-245 worthy.master.spec]$ sudo -i [root@ip-172-30-0-245 ~]# su - postgres Last login: Mon Jul 10 09:32:50 UTC 2017 on pts/0 -bash-4.2$ psql psql (9.6.3) Type "help" for help. postgres=#

spearous · Accepted Answer · 2017-06-28 13:27:34Z

0

I think you already caught the problem: all operations need "postgres" user, which is a user setup by postgresql syetem as the admin for database. root user can't do this.

One of the way, from my previous experience, is: trying to restart the service (sudo service postgresqlxxx restart). If there is something wrong, the restart process will report error.

As I said, this user is admin of database, and you can't get around of it. If all of those don't work, then just reinstall it using "sudo yum install "

answered Jun 28, 2017 at 13:27

spearous

471 silver badge5 bronze badges

Collectives™ on Stack Overflow

Install and use PostgreSQL 9.3 on Amazon EC2

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related