05/13/2018
DISCLAIMER: This is for postgresql 10 and ubuntu 18.04, and may or may not work for other versions. PS: If you have been tampering with the language settings lately, please tell me, because there may be a connection with the postgres failure
Yesterday I was having the exact same problem, and nobody on the entire internet could help me, so I went rogue... And it worked!
First and foremost, if you have any database with data you are interested in keeping, I cant help you there; you will have to figure out a way to backup all your data.
Now onto the actual steps (this is exactly what I did, the steps between [ ]
you may skip):
sudo apt remove --purge postgres*
[ sudo apt remove --purge pg* ]
sudo apt autoremove
sudo apt autoclean
sudo apt clean
[ sudo find / -name "*postgres*" -type f -delete ] -> this may delete any backups
Now make sure you have this line in /etc/apt/sources.list
deb http://cz.archive.ubuntu.com/ubuntu bionic main
If you don't, just add it... Lets continue:
sudo apt update
sudo apt upgrade
sudo apt install -y postgresql-10 postgresql-contrib postgresql-client
[ sudo apt install -y postgresql-server pgadmin3 ] -> this you may need for metasploit
[ sudo reboot ]
Now you have to check if the necessary directories were created:
/etc/postgresql/10/main
-> config files
/usr/lib/postgresql/10/bin
-> scripts & executables
/var/run/postgresql
-> temporary files
/var/lib/postgresql/10/main
-> folders
If any of those don't exist, I cant help you. You also need to make sure the user postgres
exists Lets continue:
sudo chown root /usr/lib/postgresql -R
sudo chgrp root /usr/lib/postgresql -R
sudo chmod 755 /usr/lib/postgresql -R
Now go edit /etc/environment
and add this to the PATH: /usr/lib/postgresql/10/bin
sudo chown postgres /etc/postgresql -R
sudo chgrp postgres /etc/postgresql -R
sudo chmod 700 /etc/postgresql -R
sudo echo "" > /etc/postgresql/10/main/postgresql.log
sudo chown postgresql /etc/postgresql/10/main/postgresql.log
sudo chgrp postgresql /etc/postgresql/10/main/postgresql.log
sudo chmod 700 /etc/postgresql/10/main/postgresql.log
sudo mkdir /var/run/postgresql/10-main.pg_stat_tmp
sudo chown postgres /var/run/postgresql/10-main.pg_stat_tmp -R
sudo chgrp postgres /var/run/postgresql/10-main.pg_stat_tmp -R
sudo chmod 700 /var/run/postgresql/10-main.pg_stat_tmp -R
Create /var/lib/postgresql/.bashrc
and write this to it
shopt -s histappend
HISTSIZE=1000
HISTFILESIZE=2000
shopt -s checkwinsize
case "$TERM" in
xterm-color|*-256color) color_prompt=yes;;
esac
force_color_prompt=yes
if [ -n "$force_color_prompt" ]; then
if [ -x /usr/bin/tput ] && tput setaf 1 >&/dev/null; then
color_prompt=yes
else
color_prompt=
fi
fi
if [ "$color_prompt" = yes ]; then
PS1='${debian_chroot:+($debian_chroot)}\[\033[01;32m\]\u@\h\[\033[00m\]:\[\033[01;34m\]\w\[\033[00m\]\$ '
else
PS1='${debian_chroot:+($debian_chroot)}\u@\h:\w\$ '
fi
unset color_prompt force_color_prompt
if [ -x /usr/bin/dircolors ]; then
test -r ~/.dircolors && eval "$(dircolors -b ~/.dircolors)" || eval "$(dircolors -b)"
alias ls='ls --color=auto'
alias grep='grep --color=auto'
alias fgrep='fgrep --color=auto'
alias egrep='egrep --color=auto'
fi
alias ll='ls -alF'
alias la='ls -A'
alias l='ls -CF'
if ! shopt -oq posix; then
if [ -f /usr/share/bash-completion/bash_completion ]; then
. /usr/share/bash-completion/bash_completion
elif [ -f /etc/bash_completion ]; then
. /etc/bash_completion
fi
fi
source /etc/environment
And then:
sudo chown postgresql /var/lib/postgresql/.bashrc
sudo chgrp postgresql /var/lib/postgresql/.bashrc
sudo chmod 664 /var/lib/postgresql/.bashrc
[ sudo reboot ]
And now for the final part:
sudo su
su postgres
To start postgresql:
pg_ctl start -D /etc/postgresql/10/main -l /etc/postgresql/10/main/postgresql.log
To end it:
kill $(cat /var/run/postgresql/10-main.pid)
Basic configuration (enter the postgresql
interpreter):
psql
\du+
-> list postgresql users
\l
-> list postgresql databases
createuser
and dropuser
-> self explanatory
createdb
and dropdb
-> self explanatory
EXTRA: METASPLOIT
If anyone reading this needs postgresql for metasploit, you can follow this link (its for 16.04 but works fine in 18.04), there is a mistake however: At some point, you will need to run this:
rvm --default use ruby-${RUBY_VERSION}@metasploit-framework gem pristine --all
Instead of the suggested:
rvm --default use ruby-${RUByVERSION}@metasploit-framework
Furthermore, if you run into trouble with the ruby bundle install part, this command may save your life: gem pristine --all
$ sudo systemctl status postgresql ● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Sun 2018-05-06 06:19:47 UTC; 2h 13min ago Process: 15349 ExecStart=/bin/true (code=exited, status=0/SUCCESS) Main PID: 15349 (code=exited, status=0/SUCCESS) May 06 06:19:47 myapp systemd[1]: Starting PostgreSQL RDBMS... May 06 06:19:47 myapp systemd[1]: Started PostgreSQL RDBMS.