User Tools

Site Tools


build:postgres

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
build:postgres [2014/04/28 13:33]
91.200.13.41 8 or http://soltisadvisors.com/buy/diflucan-generic.html http://soltisadvisors.com/buy/diflucan-generic.html or 255117 or http://soltisadvisors.com/buy/purchase-diflucan.html http://soltisadvisors.com/buy/purchase-diflucan.html or xdl or http://soltisad
build:postgres [2014/09/03 14:12] (current)
Admin old revision restored (2012/12/31 16:52)
Line 1: Line 1:
-8 or http://​soltisadvisors.com/buy/​diflucan-generic.html http://soltisadvisors.com/buy/​diflucan-generic.html or  255117 or http://soltisadvisors.com/buy/​purchase-diflucan.html http://soltisadvisors.com/​buy/​purchase-diflucan.html or  xdl or http://soltisadvisors.com/​buy/​diflucan-tablets.html http://soltisadvisors.com/buy/diflucan-tablets.html or  pxyu or http://​soltisadvisors.com/buy/​diflucan-prescription.html http://​soltisadvisors.com/​buy/​diflucan-prescription.html or  jisurz or http://​soltisadvisors.com/​buy/​diflucan-no-prescription.html http://​soltisadvisors.com/​buy/​diflucan-no-prescription.html or  knyqt or +====== PostgreSQL ====== 
 + 
 +While MySQL is the most popular Open Source SQL database, PostgreSQL is more powerful and matureSo while many Open Source projects only support MySQL, we prefer to use PostgreSQL when possible. 
 + 
 + 
 +===== Installation ===== 
 + 
 +<code bash> 
 +# Install all the pieces required for PostgreSQL server and client. 
 +sudo apt-get install postgresql postgresql-doc postgresql-client 
 + 
 +# Install drivers for PHP. 
 +sudo apt-get install php5-pgsql php-mdb2-driver-pgsql 
 +</code> 
 + 
 + 
 +===== Configuration ===== 
 + 
 +<code bash> 
 +# Make Postgres require passwords when accessing locally (via UNIX-domain socket). 
 +sudo sed -e 's/\(local\W*all\W*all\W*\)ident/\1md5/' ​-/etc/postgresql/8.4/main/pg_hba.conf  
 +sudo service postgresql reload 
 + 
 +# Create a password file for root. 
 +if sudo [ ! -/root/.pgpass ]; then 
 +    sudo sh -c "echo '#​hostname:​port:​database:​username:password'​ >/root/.pgpass"​ 
 +    sudo chmod 600 /root/.pgpass 
 +fi 
 +</code> 
 + 
 + 
 +===== Testing ===== 
 + 
 +<code bash> 
 +# Set up some variables to use in our testing. 
 +POSTGRESQL_TEST_DB='​postgres_test'​ 
 +POSTGRESQL_TEST_USER='​postgres_test_user'​ 
 +POSTGRESQL_TEST_PASSWORD="​$(openssl rand 15 -base64 | tr '+/' '-_'​))"​ 
 + 
 +# Create a userCould also do this via psql"​CREATE USER $POSTGRESQL_TEST_USER ENCRYPTED PASSWORD '​$POSTGRESQL_TEST_PASSWORD';"​. 
 +sudo sudo -u postgres createuser ​-SDR $POSTGRESQL_TEST_USER 
 + 
 +# Create and test using a test databaseCould also do this via psql"​CREATE DATABASE $POSTGRESQL_TEST_DB OWNER $POSTGRESQL_TEST_USER;"​. 
 +sudo sudo -u postgres createdb ​--owner $POSTGRESQL_TEST_USER $POSTGRESQL_TEST_DB 
 + 
 +# Make sure that the newly created database shows up in the list of databases. 
 +sudo sudo -u postgres psql -l 
 + 
 +# Create a DB table, and get some metadata about it. 
 +sudo sudo -u postgres psql $POSTGRESQL_TEST_DB <<"​EOF"​ # interactive 
 +  SELECT datname FROM pg_database;​ 
 +  SELECT table_name FROM information_schema.tables WHERE table_schema='​public';​ 
 +  CREATE TABLE test (id INTEGER, name VARCHAR); 
 +  SELECT table_name FROM information_schema.tables WHERE table_schema='​public';​ 
 +  SELECT column_name FROM information_schema.columns WHERE table_name ='​test';​ 
 +EOF 
 + 
 +# Remove the user and database to clean up. Note that you have to drop the DB before the owner of that DB. 
 +sudo sudo -u postgres dropdb $POSTGRESQL_TEST_DB 
 +sudo sudo -u postgres dropuser $POSTGRESQL_TEST_USER 
 +</​code>​ 
 + 
 + 
 +===== TODO ===== 
 + 
 +See if we actually use the TCP port in any of our clients (because most local clients will use the UNIX domain socket)If not, see if we can disable listening on the TCP port. 
 + 
 +Enable SSL. This doesn'​t matter too much, as long as we're only listening locally. But it looks like it's pretty simple to set up. 
build/postgres.txt · Last modified: 2014/09/03 14:12 by Admin