Linux/postgresql

POSTGRES MANUAL
=============================================================================================
INSTALLATION
=============================================================================================
sudo apt-get install postgresql-9.1 postgresql-client-9.1 postgresql-contrib-9.1
Now we need to reset the password for the ‘postgres’ admin account for the server
sudo su postgres -c psql template1
template1=# ALTER USER postgres WITH PASSWORD ‘password’;
template1=# \q
That alters the password for within the database, now we need to do the same for the unix user ‘postgres’:
sudo passwd -d postgres

=============================================================================================
asanchez75@ubuntu:/var/www$ sudo diff -u /etc/postgresql/9.1/main/postgresql.conf.backup /etc/postgresql/9.1/main/postgresql.conf
--- /etc/postgresql/9.1/main/postgresql.conf.backup	2012-03-05 09:31:32.717231431 -0500
+++ /etc/postgresql/9.1/main/postgresql.conf	2012-03-05 09:32:12.805230393 -0500
@@ -56,7 +56,7 @@

 # - Connection Settings -

-#listen_addresses = 'localhost'		# what IP address(es) to listen on;
+listen_addresses = '*'		# what IP address(es) to listen on;
 					# comma-separated list of addresses;
 					# defaults to 'localhost', '*' = all
 					# (change requires restart)
@@ -81,7 +81,7 @@
 #ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH'	# allowed SSL ciphers
 					# (change requires restart)
 #ssl_renegotiation_limit = 512MB	# amount of data between renegotiations
-#password_encryption = on
+password_encryption = on
 #db_user_namespace = off

 # Kerberos and GSSAPI
=============================================================================================
asanchez75@ubuntu:/var/www$ sudo diff -u /etc/postgresql/9.1/main/pg_hba.conf.backup  /etc/postgresql/9.1/main/pg_hba.conf
--- /etc/postgresql/9.1/main/pg_hba.conf.backup	2012-03-05 09:33:30.057228397 -0500
+++ /etc/postgresql/9.1/main/pg_hba.conf	2012-03-05 10:28:29.713143070 -0500
@@ -89,7 +89,7 @@
 # "local" is for Unix domain socket connections only
 local   all             all                                     peer
 # IPv4 local connections:
-host    all             all             127.0.0.1/32            md5
+host    all             all             127.0.0.1/32            trust
 # IPv6 local connections:
 host    all             all             ::1/128                 md5
 # Allow replication connections from localhost, by a user with the
@@ -97,3 +97,5 @@
 #local   replication     postgres                                peer
 #host    replication     postgres        127.0.0.1/32            md5
 #host    replication     postgres        ::1/128                 md5
 host      all            all            xx.xxx.xxx.0/24        trust
+
+
=============================================================================================
LINKS FOR INSTALLATION
=============================================================================================
http://www.cyberciti.biz/faq/psql-fatal-ident-authentication-failed-for-user/
http://www.ubuntugeek.com/howto-setup-database-server-with-postgresql-and-pgadmin3.html
http://nixcraft.com/databases-servers/886-howto-list-display-databases-postgresql-pgsql-server.html
=============================================================================================
MIGRATING FROM MYSQL TO POSTGRESQL
=============================================================================================
http://onestoryeveryday.com/mysql-to-postgresql-conversionmigration.html
https://github.com/tardate/mysql2postgres

Use this tool (first of all, install rubygems)
gem install mysql2psql

Next, run script and within its current folder you will find

mysql2psql.yml

whose content should be something like

----------------------------------------------------------------------------------------------
mysql:
 hostname: localhost
 port: 3306
 socket: /var/run/mysqld/mysqld.sock
 username: root
 password: "123456"
 database: oldmtnforum

destination:
 # if file is given, output goes to file, else postgres
 file:
 postgres:
  hostname: localhost
  port: 5432
  username: postgres
  password: 123456
  database: oldmtnforum

# if tables is given, only the listed tables will be converted.  leave empty to convert all tables.
#tables:
#- table1
#- table2
# if exclude_tables is given, exclude the listed tables from the conversion.
#exclude_tables:
#- table3
#- table4


# if supress_data is true, only the schema definition will be exported/migrated, and not the data
supress_data: false

# if supress_ddl is true, only the data will be exported/imported, and not the schema
supress_ddl: false

# if force_truncate is true, forces a table truncate before table loading
force_truncate: false

----------------------------------------------------------------------------------------------
Be sure about your parameters, for example I use
socket: /var/run/mysqld/mysqld.sock
because I was using UBUNTU

====================================
LOGIN
====================================

su postgres (to log in as user named postgres)
psql -d mydatabase (to lod directly to one database)
psql (to enter to console and create data as user postgres)

=================================================================
pg_dump
=================================================================
pg_dump oldmtnforum -U postgres -W -h localhost > oldmtnforum2.sql
pg_dump bd_can -U postgres -W -h localhost | gzip -c > bd_can.sql.gz
pg_dump --host localhost --port 5432 --username "postgres" --format tar --blobs --verbose --file "/home/asanchez75/Trash/oldmtnforum.psql" "oldmtnforum"
=================================================================
pg_restore
=================================================================
psql -U postgres -d test_can < /home/asanchez75/Trash/bd_can.sql
pg_restore --host localhost --port 5432 --username "postgres" --dbname "test" --verbose "/home/asanchez75/Trash/oldmtnforum.psql"

=================================================================
installation of pdo_pgsql
=================================================================

http://cpanelforums.net/php-recompilation-errors/

checking for PostgreSQL support for PDO... yes, shared
checking for pg_config... not found
configure: error: Cannot find libpq-fe.h. Please specify correct PostgreSQL installation path

Fix:
yum install postgresql-devel

=====================================

http://drupal.org.es/node/1649

Primero tienes que instalar el plugin para PDO
Enviado por waspper el 15 Febrero, 2011 - 21:18.

En cuanto a PDO, verifica que tines instalado algo como esto:
- php-pdo
- php-pdo_pgsql

Los instalas, luego reinicias Apache y PostgreSQL asi:
# su -
# password:
# service httpd restart
# service postgresql restart

====================================================================
yum install postgresql-devel
pecl install pdo_pgsql

======================================================================
working on SCHEMAS

template_postgis=# SET SEARCH_PATH TO "ecosistemas","$user","public";
template_postgis=# SHOW SEARCH_PATH;
\dn

======================================================================
check commands

execute \?