CODEWITHSUNDEEP
sqlbashpostgresqlshelldebian
Jimmy
Jimmy

Reputation: 12487

Error in creation of postgresql from bash - Install & Import

I am trying to automate the install of debian with postgreSQL but I'm running into issues with my script. The database import of schema.sql into the db1 doesn't seem to be working, and I'm not sure if I even created the database correctly.

This is the code I am using:

 # POSTGRES
apt-get install -y postgresql
echo "CREATE ROLE deploy LOGIN ENCRYPTED PASSWORD '$APP_DB_PASS';" | sudo -u postgres psql
su postgres -c "createdb db1 --owner deploy"
su postgres -c "createdb db2 --owner deploy"
service postgresql reload

# IMPORT SQL
psql --username=postgres spider < /etc/schema.sql

When I try to see if the database is created I get the following errors and the SQL import didn't seem to work.

root@li624-168:/etc/app# psql -U root spider                                                        
psql: FATAL:  role "root" does not exist

root@li624-168:/etc//app# psql -U deploy spider                                                      
psql: FATAL:  Peer authentication failed for user "deploy"

Can anyone tell me please where I have gone wrong?

Upvotes: 2

Views: 526

Answers (2)

razcor
razcor

Reputation: 354

Postgres databases are owned by Linux users. So, you need to create an user in postgres tha have the same name of your Linux user. then, you have to use the new user to create your db. Example:

My linux account is razcor

sudo su postgres -c 'createuser -d -E -R -S razcor'

this creates a postgres user

sudo su razcor -c "createdb db1 --owner razcor"

this creates my db

result:

razcor@ubuntu:~$ psql -U razcor db1

psql (8.4.17)
Type "help" for help.

db1=>

In your case create a user named: root

@Richard Huxton: yes, I agree.

Upvotes: 0

Richard Huxton
Richard Huxton

Reputation: 22893

Firstly, make sure you check result codes when executing commands. You can abort your bash script by adding set -e at the top. If any single command fails it will stop immediately.

Secondly, take another look at the error message:

Peer authentication failed for user "deploy"

You're trying to login as "deploy" and it seems to recognize the user-name. However, your operating-system user is not called "deploy", so peer auth fails. It looks like you want to login using a password, so set up your pg_hba.conf file to allow that.

Upvotes: 1

Related Questions