PostgreSQL, psql, CentOS, Linux setup and notes - mhulse/mhulse.github.io GitHub Wiki

Get psql version:

# Server version:
$ pg_config --version
# Client version:
$ psql --version

Create database from the default shell:

$ createdb example_db
# or:
$ createdb -O rolename example_db

… or from the psql shell using sql (preferred):

# create database awarehealth_payment with template template0;

Drop database from the default shell:

$ dropdb example_db

Drop a table using sql:

# drop table products cascade;

Create user from the psql shell using sql:

# create user test_user_1 with password 'xxxx';

Grant privledges to user for database:

# grant all privileges on database example_db to test_user_1;

Connect to databse:

# \c example_db

You can view your databases using:

# \l

List all tables for an existing database you are already connected to:

# \dt

List schema tables:

# \dt+

list all schemas with:

# \dn

List down all the tables in an attached database:

# \d

Show all columns for a given table including type information, references and key constraints:

# \d [table_name]

List users:

# \dt

If you’re all done, you can exit using:

# \q

These instructions are for macOS running Sierra.

Download and install Postgres.app.

Download and install PGAdmin.

Download and install Hosts.prefpane.

For local Apache/PHP development, I use XAMPP (installation and setup notes here).

Put this in your .bash_profile:

# Instead of doing this at the prompt:
# $ '/Applications/Postgres.app/Contents/Versions/9.6/bin'/psql -p5432
# You can now do this:
# $ psql
export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/latest/bin

Using the Hosts prefpane in your system preferences, you can “spoof” a site of your choosing if it is of help to your development to fake a domain like baz.org; set the IP to 127.0.0.1 and the hostname to baz.org. Close or lock the prefpane when done.

Next, setup an XAMPP (Apache) vhost:

<VirtualHost *:80>
	DocumentRoot "/path/to/project/api.baz.org"
	ServerName api.baz.org
	ServerAlias www.api.baz.org
	ErrorLog "logs/api.baz.org-error.log"
	CustomLog "logs/api.baz.org-access.log" combined
	#DirectoryIndex index.html
	<Directory "/path/to/project/api.baz.org">
		IndexOptions +FancyIndexing NameWidth=*
		Options -Indexes +Includes +FollowSymLinks +MultiViews
		AllowOverride All
		Order allow,deny
		Allow from all
		Require all granted
	</Directory>
</VirtualHost>

Restart Apache.

Note: If you want to make your life easier, put these aliases in your user’s profile:

# XAMPP shortcuts:
alias xampp="sudo /Applications/XAMPP/xamppfiles/xampp restart"
alias xampp_stop="sudo /Applications/XAMPP/xamppfiles/xampp stop"
alias xampp_start="sudo /Applications/XAMPP/xamppfiles/xampp start"
alias xo='function __xo() { xampp && open "http://"$1; }; __xo'

In your browser, visit http://api.baz.org.

The local installation of baz.org’s API should appear.

If there is a live version of the site at the same http://api.baz.org address, disable the spoof in the Hosts prefpane (there’s a checkbox next to the host entry; toggle this to enable and disable the spoof).

Datbases:

1. example_db

Users:

1. myuser

Create those using PGAdmin by right clicking on the “Login/Group Roles” under “Postgres.app”.

Or, navigate to directory that contains your .sql dump and launch psql command line:

$ psql -p5432
create user test_user_1 with password 'xxxx';
create database example_db with template template0;
grant all privileges on database example_db to test_user_1;

Next, connect to your database and import the sql:

# \c example_db
# \i example_db.sql;

Alternatively, you can use psql to import a dumped sql file:

# \q
''/Applications/Postgres.app/Contents/Versions/9.6/bi'n/psql -p5432 -f example_db.sql example_db

Check CentOS version at command line:

$ cat /etc/*-release
CentOS Linux release 7.2.1511 (Core)

Install PostgreSQL:

$ sudo yum install postgresql-server postgresql-contrib

Initialize database:

$ sudo postgresql-setup initdb
Initializing database ... OK

Next, configure PostgreSQL to allow password authentication:

sudo nano /var/lib/pgsql/data/pg_hba.conf

… and change ident (below) to md5:

# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident

Save and close file.

Add Linux user:

# List local users:
$ cut -d: -f1 /etc/passwd
# Add a new user:
$ sudo adduser myuser
# Remove a user:
$ sudo userdel myuser && rm -r /home/myuser
# Set password:
$ sudo passwd myuser
Changing password for user myuser.
New password: xxxx
Retype new password: xxxx

Now start and enable PostgreSQL:

$ sudo systemctl start postgresql
$ sudo systemctl enable postgresql

# Set password for postgres user:
$ sudo -u postgres psql postgres
postgres=# \password postgres
Enter new password: xxxx
Enter it again: xxxx
postgres=# \q

Next, log into Postgres and fire up the psql prompt:

$ sudo -i -u postgres
[sudo] password for mylinuxuserlogin:
-bash-4.2$ psql
psql (9.2.15)
Type "help" for help.

postgres=#

If that works, type \q to exit psql and, for greater flexibility, let’s create a new role:

$ createuser --interactive
Enter name of role to add: myuser
Shall the new role be a superuser? (y/n) y

Switch to user myuser:

$ sudo -i -u myuser

… fire up the psql prompt:

$ psql

You can connect to a specific database like so:

$ psql -d postgres

Get info about current user and database you’re connected to:

myuser=# \conninfo
You are connected to database "myuser" as user "myuser" via socket in "/var/run/postgresql" at port "5432".

Next, setup the database:

myuser=# create user test_user_1 with password 'xxxx';
myuser=# create database example_db with template template0;
myuser=# grant all privileges on database example_db to test_user_1;

At any point you can upload your sql dump; I put mine in /tmp directory using SFTP.

Now, connect to your database and import the sql:

myuser=# \c example_db
myuser=# \i /tmp/example_db.sql;

Alternatively, you can use psql to import a dumped sql file:

myuser=# \q
$ psql -p5432 -f /tmp/example_db.sql example_db

Note: To make life easier, you can switch to the root user using sudo -i; doing so will allow you to get stuff done eaiser! :D

Check to make sure PHP is installed:

$ php -i
-bash: php: command not found

If you see the same output as above, then install PHP:

$ sudo yum install php
$ sudo yum install php-xml
$ sudo yum install php-pear
$ yum install php-pdo
$ yum install php-pdo_pgsql
# May not need:
$ yum install php-pdo_mysql
# Restart Apache:
$ apachectl restart

Pear module dependencies:

$ pear channel-update pear.php.net
$ pear install --alldeps Mail
$ pear install --alldeps Mail_Mime

Install git:

$ sudo yum install git

Next, create the project directory:

$ cd /var/www
$ sudo mkdir example-site
$ sudo chown foo:foo example-site

Replace foo:foo with your linux user.

Now clone the repo using https:

$ git clone https://[email protected]/ieq/example-site.git example-site/

Next, we’re going to setup an Apache vhost for /var/www/example-site/sub_direcotry_root:

$ cd /etc/httpd/conf.d
$ sudo touch example-site.baz.com.conf
$ sudo nano example-site.baz.com.conf

… and paste this:

<VirtualHost *:80>
    DocumentRoot "/var/www/example-site/sub_direcotry_root/api.baz.org"
    ServerName example-site.baz.com
    ServerAlias www.example-site.baz.com
    ErrorLog "logs/example-site.baz.com-error.log"
    CustomLog "logs/example-site.baz.com-access.log" combined
    DirectoryIndex index.html index.php
    <Directory "/var/www/example-site/sub_direcotry_root/api.baz.org">
        IndexOptions +FancyIndexing NameWidth=*
        Options -Indexes +Includes +FollowSymLinks +MultiViews
        AllowOverride All
        Order allow,deny
        Allow from all
        Require all granted
    </Directory>
</VirtualHost>

… save and close (CTRL+O and CTRL+X).

Restart apache:

$ sudo apachectl restart

Visit http://example-site.baz.com to see if the VirtualHost worked.

If not, do:

$ sudo -i
$ tail -f /var/log/httpd/example-site.baz.com-error.log -n 100

… and fix any errors you see in the error log.

Finally, at the command line, run:

# httpd_can_network_connect (HTTPD Service):: Allow HTTPD scripts and modules to connect to the network.
$ setsebool -P httpd_can_network_connect on
$ setsebool -P httpd_can_sendmail on

Note that you can check the value of the above settings simply by omitting the boolean:

getsebool httpd_can_network_connect
getsebool httpd_can_sendmail

• How To Install and Use PostgreSQL on CentOS 7
• How To Use Roles and Manage Grant Permissions in PostgreSQL on a VPS