Git + Eclipse + Php

The basic steps are:

  1. Install EGit
  2. Import Git project
  3. Create a new PHP project, based on existing code (ie, your Git clone)

 

1. Install EGit

  1. In the Help menu, go to Install New Software
  2. Select your main Update Site from the “Work with” drop-down menu (usually starts with http://download.eclipse.org/releases/ )
  3. Where it says “type filter text”, type “git”
  4. Click the EGit checkbox (you may also like to select the EGit Mylyn GitHub Connector!)
  5. Click Finish & carry out the last few self-explanatory steps.

 

2. Import Git project

  1. Right-click and select Import
  2. Select Git > Projects from Git
    (or if you’re using the Eclipse for GitHub plugin (see below) select “Repositories from GitHub”)
  3. Select Local or URI and click Next
  4. Paste in your Git URI
  5. Branch selection – select branches you want and click Next
  6. Local destinationcopy the installation path and click Next
  7. Select Use the New Project wizard and click Finish

 

3. Create a new PHP project

  1. Select PHP Project in the New Project which opens and click Next
  2. Enter your Project Name
  3. Under Contents, select “Create project at existing location
  4. Paste in your Git repo installation path; or browse to, and select the directory your Git repository has been saved in
  5. Click Finish

That’s it!

As a bonus, here’s a couple of Eclipse plugins you may find useful:

  • Symfony2 (includes Twig & YAML support)
  • eZ Publish (includes template syntax highlighting & validation, content class browser, and template operator wizard)
  • Eclipse for GitHub (download Eclipse + EGit + EGit Mylyn GitHub Connector)

Install PostGreSql

In this exercise, we’ll go thru installing PostgreSQL 9.3 on a CentOS 6 64-bit box. We’ll cover upgrading in a later article. For the rest of this article, we’ll go over configuring yum to use the PostgreSQL PGDG Yum repository found at http://yum.postgresql.org , which has the latest and greatest of 9.3. It’s been a while since we wrote step by step instructions for installing with Yum.

Note: PostGIS 2.1.2 release is eminent, so you might want to wait till we release and Yum is updated before you install/upgrade.
Last we did was An almost idiot’s guide to Install PostgreSQL 9.0 with Yum which is now very dated.

Installing PostgreSQL 9.3 from Yum repository

The list of latest PostgreSQL versions and repos for Fedora 18-20, Red Hat 5-6, CentOS 5-6, and Scientific Linux 5-6 are located at http://yum.postgresql.org/repopackages.php.

  1. Figure out which OS you are on. Note the PGDG repository 9.3 only has binaries for the aforementioned, though for lower versions you can still get 9.2, 9.1 etc..First run

    uname -a
    Mine happens to return: Linux 30266-3-2358424 2.6.32-358.2.1.el6.x86_64 #1 SMP Wed Mar 13 00:26:49 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux so I know its a 64-bit because of the X86_64 at the end.
    cat /etc/redhat-release
    Mine returns:
    CentOS release 6.4 (Final)

  2. Install the right rpms from http://yum.postgresql.org/repopackages.php for your platform (note the hyperlink url) since you’ll need it for rpm addition. In our case:
    sudo rpm -ivh http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
  3. sudo yum list | grep postgresql should give you entries that look something like below and the ones we want are marked with pgdg93.
    bacula-director-postgresql.x86_64        5.0.0-12.el6                   base
    bacula-storage-postgresql.x86_64         5.0.0-12.el6                   base
    freeradius-postgresql.x86_64             2.1.12-4.el6_3                 base
    postgresql.i686                          8.4.20-1.el6_5                 updates
    postgresql.x86_64                        8.4.20-1.el6_5                 updates
    postgresql-contrib.x86_64                8.4.20-1.el6_5                 updates
    postgresql-devel.i686                    8.4.20-1.el6_5                 updates
    postgresql-devel.x86_64                  8.4.20-1.el6_5                 updates
    postgresql-docs.x86_64                   8.4.20-1.el6_5                 updates
    postgresql-jdbc.noarch                   8.4.701-8.el6                  base
    postgresql-libs.i686                     8.4.20-1.el6_5                 updates
    postgresql-libs.x86_64                   8.4.20-1.el6_5                 updates
    postgresql-odbc.x86_64                   08.04.0200-1.el6               base
    postgresql-plperl.x86_64                 8.4.20-1.el6_5                 updates
    postgresql-plpython.x86_64               8.4.20-1.el6_5                 updates
    postgresql-pltcl.x86_64                  8.4.20-1.el6_5                 updates
    postgresql-server.x86_64                 8.4.20-1.el6_5                 updates
    postgresql-test.x86_64                   8.4.20-1.el6_5                 updates
    postgresql93.x86_64                      9.3.4-1PGDG.rhel6              pgdg93
    postgresql93-contrib.x86_64              9.3.4-1PGDG.rhel6              pgdg93
    postgresql93-debuginfo.x86_64            9.3.4-1PGDG.rhel6              pgdg93
    postgresql93-devel.x86_64                9.3.4-1PGDG.rhel6              pgdg93
    postgresql93-docs.x86_64                 9.3.4-1PGDG.rhel6              pgdg93
    postgresql93-jdbc.x86_64                 9.3.1100-1PGDG.rhel6           pgdg93
    postgresql93-jdbc-debuginfo.x86_64       9.3.1100-1PGDG.rhel6           pgdg93
    postgresql93-libs.x86_64                 9.3.4-1PGDG.rhel6              pgdg93
    postgresql93-odbc.x86_64                 09.02.0100-1PGDG.rhel6         pgdg93
    postgresql93-odbc-debuginfo.x86_64       09.02.0100-1PGDG.rhel6         pgdg93
    postgresql93-plperl.x86_64               9.3.4-1PGDG.rhel6              pgdg93
    postgresql93-plpython.x86_64             9.3.4-1PGDG.rhel6              pgdg93
    postgresql93-pltcl.x86_64                9.3.4-1PGDG.rhel6              pgdg93
    postgresql93-server.x86_64               9.3.4-1PGDG.rhel6              pgdg93
    postgresql93-test.x86_64                 9.3.4-1PGDG.rhel6              pgdg93
    postgresql_autodoc.noarch                1.41-1.rhel6                   pgdg93
    qt-postgresql.i686                       1:4.6.2-28.el6_5               updates
    qt-postgresql.x86_64                     1:4.6.2-28.el6_5               updates
  4. To see all packages from pgdg93 do: yum list | grep pgdg93. This lists the core PostgreSQL offering and packaged PostgreSQL extensions. I’ve snipped the list a bit to show just extensions we often install:
    pgrouting_93.x86_64                      2.0.0-1.rhel6                  pgdg93
    pgrouting_93-debuginfo.x86_64            2.0.0-1.rhel6                  pgdg93
    pgxnclient.x86_64                        1.2.1-1.rhel6                  pgdg93
    :
    :
    plv8_93.x86_64                           1.4.1-1.rhel6                  pgdg93
    plv8_93-debuginfo.x86_64                 1.4.1-1.rhel6                  pgdg93
    postgis2_93.x86_64                       2.1.1-1.rhel6                  pgdg93
    postgis2_93-client.x86_64                2.1.1-1.rhel6                  pgdg93
    postgis2_93-debuginfo.x86_64             2.1.1-1.rhel6                  pgdg93
    postgis2_93-devel.x86_64                 2.1.1-1.rhel6                  pgdg93
    postgis2_93-docs.x86_64                  2.1.1-1.rhel6                  pgdg93
    postgis2_93-utils.x86_64                 2.1.1-1.rhel6                  pgdg93
    postgresql93.x86_64                      9.3.4-1PGDG.rhel6              pgdg93
    postgresql93-contrib.x86_64              9.3.4-1PGDG.rhel6              pgdg93
    postgresql93-debuginfo.x86_64            9.3.4-1PGDG.rhel6              pgdg93
    postgresql93-devel.x86_64                9.3.4-1PGDG.rhel6              pgdg93
    postgresql93-docs.x86_64                 9.3.4-1PGDG.rhel6              pgdg93
    postgresql93-jdbc.x86_64                 9.3.1100-1PGDG.rhel6           pgdg93
    postgresql93-jdbc-debuginfo.x86_64       9.3.1100-1PGDG.rhel6           pgdg93
    postgresql93-libs.x86_64                 9.3.4-1PGDG.rhel6              pgdg93
    postgresql93-odbc.x86_64                 09.02.0100-1PGDG.rhel6         pgdg93
    postgresql93-odbc-debuginfo.x86_64       09.02.0100-1PGDG.rhel6         pgdg93
    postgresql93-plperl.x86_64               9.3.4-1PGDG.rhel6              pgdg93
    postgresql93-plpython.x86_64             9.3.4-1PGDG.rhel6              pgdg93
    postgresql93-pltcl.x86_64                9.3.4-1PGDG.rhel6              pgdg93
    postgresql93-server.x86_64               9.3.4-1PGDG.rhel6              pgdg93
    postgresql93-test.x86_64                 9.3.4-1PGDG.rhel6              pgdg93
    
  5. We usually install the below. The devel is optional but needed if you want to compile some other add on later like non-packaged extensions.

    yum install postgresql93 postgresql93-server postgresql93-libs postgresql93-contrib postgresql93-devel

  6. You should get a prompt something like:
    Dependencies Resolved
    
    ============================================================================================================================================================================================================================================================================================
     Package                                                                      Arch                                                           Version                                                                   Repository                                                      Size
    ============================================================================================================================================================================================================================================================================================
    Installing:
     postgresql93                                                                 x86_64                                                         9.3.4-1PGDG.rhel6                                                         pgdg93                                                         1.0 M
     postgresql93-contrib                                                         x86_64                                                         9.3.4-1PGDG.rhel6                                                         pgdg93                                                         483 k
     postgresql93-devel                                                           x86_64                                                         9.3.4-1PGDG.rhel6                                                         pgdg93                                                         1.5 M
     postgresql93-libs                                                            x86_64                                                         9.3.4-1PGDG.rhel6                                                         pgdg93                                                         190 k
     postgresql93-server                                                          x86_64                                                         9.3.4-1PGDG.rhel6                                                         pgdg93                                                         4.1 M
    Installing for dependencies:
     libxslt                                                                      x86_64                                                         1.1.26-2.el6_3.1                                                          base                                                           452 k
     uuid                                                                         x86_64                                                         1.6.1-10.el6                                                              base                                                            54 k
    
    Transaction Summary
    ============================================================================================================================================================================================================================================================================================
    Install       7 Package(s)
    
    Total download size: 7.7 M
    Installed size: 32 M
    Is this ok [y/N]:

    Type: yIt should then download the packages and install them with a Complete! exclamation when done.

Installing PostGIS binaries

PostGIS unfortunately has a lot of dependencies because of GDAL and JSON dependency introduced in 2.0 and while most of the time it’s as easy as adding:

sudo yum install postgis2_93It often is not. As with this virgin box we tried installing on we got these errors:

Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
           Requires: libcfitsio.so.0()(64bit)
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
           Requires: libspatialite.so.2()(64bit)
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
           Requires: libnetcdf.so.6()(64bit)
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
           Requires: libfreexl.so.1()(64bit)
Error: Package: postgis2_93-client-2.1.1-1.rhel6.x86_64 (pgdg93)
           Requires: libjson.so.0()(64bit)
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
           Requires: libarmadillo.so.3()(64bit)
Error: Package: postgis2_93-2.1.1-1.rhel6.x86_64 (pgdg93)
           Requires: hdf5
Error: Package: postgis2_93-2.1.1-1.rhel6.x86_64 (pgdg93)
           Requires: json-c
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
           Requires: libhdf5.so.6()(64bit)
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
           Requires: libdap.so.11()(64bit)
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
           Requires: libCharLS.so.1()(64bit)
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
           Requires: libdapserver.so.7()(64bit)
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
           Requires: libdapclient.so.3()(64bit)
Error: Package: postgis2_93-2.1.1-1.rhel6.x86_64 (pgdg93)
           Requires: libjson.so.0()(64bit)
Error: Package: gdal-libs-1.9.2-4.el6.x86_64 (pgdg93)
           Requires: libgta.so.0()(64bit)

The issue is the dependency packages are not distributed with yum postgresql repo. Most of these are in the RHEL Extra Packages for Enterprise Linux (EPEL) repo. So if you get these errors, do the following:

  • The master mirror are located at http://dl.fedoraproject.org/pub/epel.

    For CentOS 6, RHEL 6 64-bit use:

    sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpmFor CentOS 6, RHEL 6 32-bit use:

    sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpmFor CentOS 5, RHEL 5 64-bit use:

    sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/5/x86_64/epel-release-5-4.noarch.rpmFor CentOS 5, RHEL 5 32-bit use:

    sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm

  • Since we are on CentOS 6 64-bit we went with the first set.

    sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpmYou should get a screen that looks something like this if successful

    Retrieving http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
    warning: /var/tmp/rpm-tmp.C2K8LE: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
    Preparing...                ########################################### [100%]
       1:epel-release           ########################################### [100%]
  • Now we repeat the above command again:

    sudo yum install postgis2_93

  • After the above, you should see a screen that looks like this:
    Dependencies Resolved
    
    ============================================================================================================================================================================================================================================================================================
     Package                                                                    Arch                                                           Version                                                                    Repository                                                       Size
    ============================================================================================================================================================================================================================================================================================
    Installing:
     gdal-libs                                                                  x86_64                                                         1.9.2-4.el6                                                                pgdg93                                                          3.8 M
     postgis2_93                                                                x86_64                                                         2.1.1-1.rhel6                                                              pgdg93                                                          5.0 M
    Installing for dependencies:
     CharLS                                                                     x86_64                                                         1.0-1.el6                                                                  epel                                                             73 k
     armadillo                                                                  x86_64                                                         3.800.2-1.el6                                                              epel                                                             16 k
     atlas                                                                      x86_64                                                         3.8.4-2.el6                                                                base                                                            2.8 M
     blas                                                                       x86_64                                                         3.2.1-4.el6                                                                base                                                            321 k
     cfitsio                                                                    x86_64                                                         3.240-3.el6                                                                epel                                                            498 k
     freexl                                                                     x86_64                                                         1.0.0d-1.el6                                                               epel                                                             31 k
     geos                                                                       x86_64                                                         3.4.2-1.rhel6                                                              pgdg93                                                          529 k
     giflib                                                                     x86_64                                                         4.1.6-3.1.el6                                                              base                                                             37 k
     hdf5                                                                       x86_64                                                         1.8.5.patch1-7.el6                                                         epel                                                            1.4 M
     json-c                                                                     x86_64                                                         0.10-2.el6                                                                 epel                                                             24 k
     lcms-libs                                                                  x86_64                                                         1.19-1.el6                                                                 base                                                            100 k
     libdap                                                                     x86_64                                                         3.11.0-1.el6                                                               epel                                                            445 k
     libgeotiff                                                                 x86_64                                                         1.4.0-1.el6                                                                pgdg93                                                          749 k
     libgta                                                                     x86_64                                                         1.0.2-2.el6                                                                epel                                                             31 k
     libspatialite                                                              x86_64                                                         2.4.0-0.6.RC4.el6                                                          epel                                                            611 k
     libtool-ltdl                                                               x86_64                                                         2.2.6-15.5.el6                                                             base                                                             44 k
     netcdf                                                                     x86_64                                                         4.1.1-3.el6.5                                                              epel                                                            4.8 M
     openjpeg-libs                                                              x86_64                                                         1.3-10.el6_5                                                               updates                                                          60 k
     poppler                                                                    x86_64                                                         0.12.4-3.el6_0.1                                                           base                                                            557 k
     poppler-data                                                               noarch                                                         0.4.0-1.el6                                                                base                                                            2.2 M
     postgis2_93-client                                                         x86_64                                                         2.1.1-1.rhel6                                                              pgdg93                                                          122 k
     proj                                                                       x86_64                                                         4.8.0-2.rhel6                                                              pgdg93                                                          172 k
     unixODBC                                                                   x86_64                                                         2.2.14-12.el6_3                                                            base                                                            378 k
     xerces-c                                                                   x86_64                                                         3.0.1-20.el6                                                               base                                                            866 k
    
    Transaction Summary
    ============================================================================================================================================================================================================================================================================================
    Install      26 Package(s)
    
    Total download size: 25 M
    Installed size: 113 M
    
    Is this ok [y/N]:

    Click y

  • After you are done you should have a screen like this:
    Installed:
      gdal-libs.x86_64 0:1.9.2-4.el6  postgis2_93.x86_64 0:2.1.1-1.rhel6
    
    Dependency Installed:

    CharLS.x86_64 0:1.0-1.el6 armadillo.x86_64 0:3.800.2-1.el6 ..

Note that after all this, you still need to enable PostGIS in each database you will want to use it in. We’ll cover that later in this article after we are done setting up the PostgreSQL service.

Installing pgRouting binaries

pgRouting is also now available via Yum. Devrim was kind enough to add CGAL to the repo to simplify the install a bit. So If you want to use pgRouting with PostGIS, it should be a simple:

yum install pgrouting_93

If you need to configure things the way you want to configure things

Skip this section if you just want to run with defaults and will only have one instance of PostgreSQL

If you plan to run multiple versions of PostgreSQL e.g. stable 9.3 and devl 9.4, on the same server (so they use different ports), or you just need to be in control of all your options, you’ll want to tweak your services scripts a bit. The best way to do that is to edit/create a file in /etc/sysconfig/pgsql/postgresql-9.3 with your preferred changes:

Note that # are remarks to you so don’t type them in

su root
vi /etc/sysconfig/pgsql/postgresql-9.3

if the file doesn’t exist — it will be blank which is okay: Click i to insert a line and start typing. I generally like to run on non-standard ports just to avoid postgresql bots blindly pinging on 5432. You also need to run on different ports if you have multiple instances running on same server. Note: PGDATA you don’t need to add unless you are unhappy with the default location.

My file when I am done will look something like this

PGPORT=5438
PGDATA=/pgdata93

Click Escape key followed by :w :q (:w saves the file and :q exits)

Creating PostgreSQL data cluster and starting the service

With the Yum setup each version of postgresql goes in its own separate folder, so you can easily have parallel installs of different versions of PostgreSQL without worring about overriding the default data folder. Devrim has the details of these changes in What-is-new-in-PostgreSQL-9.0-RPMs.html.

I’ll reiterate the key elements and consequences of these changes you need to watch out for.

Key Steps to setup

If you want the service to start up on reboot, then simply do this:

su root
service postgresql-9.3 initdb 
service postgresql-9.3 start
chkconfig --list #(to see list of services)
chkconfig postgresql-9.3 on #(to have start on bootup)

The data cluster is created by default in /var/lib/pgsql/9.3/data and the extensions folder is in /usr/pgsql-9.3/share/extension/ and the binaries are in /usr/pgsql-9.3/bin. To use psql, you can generally get away with just typing psql without the full path, but if you have multiple installations of PostgreSQL, you’re better off being explicit which one you want.

______________________________________________oooooooo_____________________________________________________

 

Aggiungere un percorso a PATH permanentemente per tutti gli utenti

I punti in cui agire sono nel dir /etc/

  • bash.bashrc
  • profile
  • environment

La soluzione preferibile:
creare un file di tipo .sh  (new-path-script.sh) in /etc/profile.d con un contenuto del tipo

PATH=new-dir-da-aggiungere:$PATH
export PATH

quindi rendiamo il file .sh creato eseguibile con

sudo chmod a+x /etc/profile.d/new-path-script.sh

oppure
aggiungere alla fine del file /etc/profile

PATH=new-dir-da-aggiungere:$PATH

oppure
modificare

/etc/environment

!!!attenzione!!! modificando questo file dovrete inserire tutti i PATH esistente poiché va a sovrascrivere le altre impostazioni di sistema

______________________________________________oooooooo_____________________________________________________

To connect to your postgres server do this:

su postgres
/usr/pgsql-9.3/bin/psql -p 5432

To install the adminpack useful for viewing logs and editing postgresql.conf from the comfort of PgAdmin, don’t forget to install the admin pack

su postgres
/usr/pgsql-9.3/bin/psql -p 5432 -c "CREATE EXTENSION adminpack;"

On startup do a:

service postgresql-9.3 status
You should see a pid runnig … message if all is good. If it says stopped, then check your /var/lib/pgsql/9.3/data/pg_log last log file which usually lists what went wrong. Often times it’s usually caused by a typo you introduced in pg_hba.conf or postgresql.conf .

su postgres
psql -p 5432 #or whatever port you chose
CREATE role regina LOGIN PASSWORD 'whateveryouwant' SUPERUSER;

At the psql prompt: type the query below to verify the location of your cluster:

SELECT setting FROM pg_settings WHERE name = 'config_file';

which should return an answer of where your configuration file is:

                 setting
-----------------------------------------
 /var/lib/pgsql/9.3/data/postgresql.conf

or The location of where you setup your cluster.then do a \q to exit the psql console

  • After that:
    cd /var/lib/pgsql/9.3/data (or the location of your data cluster)
    vi postgresql.conf
  • #make changes to memory, enable etc.

    change #listen_addresses='localhost' to listen_addresses='*' if you want postgresql to run on all ips. You can set to a specific if you have multiple ips too.

    if you need to change ports and data cluster — don’t do it in postgresql.conf — remember in /etc/sysconfig/pgsql/postgresql-9.3

  • Setup access permissions
    vi pg_hba.conf
    #make changes to access .e.g add a line below if you want to be able to access postgresql from external.
    host all all  0.0.0.0/0   md5
  • When done with all of that do:
    service postgresql-9.3 restart

Whenever you change the pg_hba.conf file, you need to do a restart or a:
service postgresql-9.3 reload
for the changes to take effect.

Creating a PostGIS/pgRouting enabled database

If you want to create a new database that is PostGIS enabled, do this:

su postgres
/usr/pgsql-9.3/bin/psql -p 5432

In psql command prompt type following:

CREATE ROLE gisdb_user LOGIN PASSWORD 'whatever';
CREATE DATABASE gisdb WITH OWNER gisdb_user;
\connect gisdb;
CREATE EXTENSION postgis;
SELECT postgis_full_version();
--if you want a side of pgRouting with PostGIS -- add this --
CREATE EXTENSION pgrouting;
SELECT * FROM pgr_version();
\q
\exit

The output of the postgis_full_version() command should be something like:

POSTGIS="2.1.1 r12113" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.6" LIBJSON="UNKNOWN" RASTER

if postgis is properly installed.The output of SELECT * FROM pgr_version();; should be something like:

 version |       tag       | build |  hash   | branch | boost
---------+-----------------+-------+---------+--------+--------
 2.0.0   | pgrouting-2.0.0 | 0     | f26831f | master | 1.41.0
WARNING: If you are running RHEL 5, as Devrim noted here, raster support was not compiled in because of dependency issues, which also means you can’t install with extensions. If you don’t have PostGIS extension support. You need to install the scripts in share/contrib/postgis-2.1 (postgis.sql, spatial_ref_sys.sql, postgis_comments.sql).
Turning off firewall or enabling PostgreSQL access

If after all those changes, you still can’t access your PostgreSQL server from pgAdmin external, verify if you have firewall running. The Image I grabbed had iptables on.

su root
service iptables status

Just for perspective, our rule table looked like this before. As you can see no rule to enable postgres access.

Table: filter
Chain INPUT (policy ACCEPT)
num  target     prot opt source               destination
1    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0           state RELATED,ESTABLISHED
2    ACCEPT     icmp --  0.0.0.0/0            0.0.0.0/0
3    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0
4    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:22
5    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:80
6    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:443
7    REJECT     all  --  0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited

Chain FORWARD (policy ACCEPT)
num  target     prot opt source               destination
1    REJECT     all  --  0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited

Chain OUTPUT (policy ACCEPT)
num  target     prot opt source               destination
service iptables stop

Verify you can connect after turning off. Okay now turn back on. The below example will enable tcp ports 5432-5438 (which we will use for postgres services) and insert the rule in the chain INPUT at row 7. Note my 10th rule is to reject all, so want all my access rules to be before that.

service iptables start
iptables -I INPUT 7 -p tcp --dport 5432:5438 -j ACCEPT
service iptables save
service iptables restart

After we were done, it looked like this:

Chain INPUT (policy ACCEPT)
num  target     prot opt source               destination
1    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0           state RELATED,ESTABLISHED
2    ACCEPT     icmp --  0.0.0.0/0            0.0.0.0/0
3    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0
4    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:22
5    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:80
6    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:443
7    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpts:5432:5438
8    REJECT     all  --  0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited

Chain FORWARD (policy ACCEPT)
num  target     prot opt source               destination
1    REJECT     all  --  0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited

Chain OUTPUT (policy ACCEPT)
num  target     prot opt source               destination

PostGreSql

Postgresql, abbreviato anche in “postgres”, è un database relazione ad oggetti molto completo.

Di seguito alcuni dei principali comandi che si possono lanciare da terminale per gestire e controllare postgresql.

  • Il servizio, il server,  è in genere chiamato postgresql o postgresql più il numero di versione
  • Il client per postgresql da terminale è psql
  • Il client grafico di postgresql è pgadmin3
  • L’utente base del database è l’utente postgres

Avviare Fermare postgresql

service postgresql

{start|stop|status|restart|condrestart| condstop|reload|force-reload|initdb}

es: service postgresql restart 
sudo service postgresql status 
/etc/init.d/postgresql

{start|stop|status|restart| condrestart|condstop|reload|force-reload|initdb}

es: /etc/init.d/postgresql status 

Postgresql running
per verificare che postgresql sia attivo

ps axf | grep [p]ostgres 

per verificare che il server stia correttamente ascoltando sulla porta prescelta si può utilizzare il comando

netstat -nlp | grep 5432
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 10723/postgres
tcp6 0 0 :::5432 :::* LISTEN 10723/postgres

Ricaricare pg_hba.conf aseguito di modifica

 
su – postgres
pg_ctl reload
postgres=# select pg_reload_conf();   ##utilizzando postgresql cli

Configurazione postgresql

Due sono i file che usualmente vengono utilizzati per la configurazione del server postgresql postgresql.conf( settaggio porta di ascolto … ) e  pg_hda.conf  ( quali users possono accedere a quali databases e ccome possono farlo ).

Accedere alla console interattiva

psql test postgres
psql postgres postgres
psql -U postgres -W postgres -h 10.0.2.15 -p 5432
o
su - postgres
psql
exit per uscire

Help

\h help comandi SQL
\h SELECT
\h CREATE INDEX  

\? help comandi psql propri della console interattiva
\h select
\q exit console
\g o punto e virgola per eseguire la query

Mostra la versione di postgres

db=# select version();
Elenca databases da console interattiva

db=# \l
query
db=#
 SELECT datname FROM pg_database;

oppure elenca databases da terminale

$ psql -l

Cambia database da console interattiva

db=# \c eurotech ;

Elenca tabelle di un database da console interattiva

db=# \d
query
db=# SELECT table_name FROM information_schema.tables WHERE table_schema = ‘public’

Elenca colonne di una tabella di un database da console interattiva

db=# \d tabella
query
db=# SELECT column_name FROM information_schema.column WHERE table_name = ‘tabella‘ ;

db=# \d+ tabella

Users

Cambiare la password di un utente ed esempio con l’utente di riferimento postgres

db=# ALTER USER user WITH PASSWORD 'userpassword';
db=# ALTER USER postgres WITH PASSWORD 'YourPostgresPassword';

Creare un nuovo utente / role

db=# CREATE USER newuser WITH password 'userpassword';

Creare un nuovo “testuser” ROLE con superuser e password:

 db=# CREATE ROLE testuser WITH SUPERUSER LOGIN PASSWORD 'test';

Creare un nuovo utente dalla shell

/usr/local/pgsql/bin/createuser newuser

Permessi
Usare GRANT consente di definire i permessi a livello di database, tabella e di colonna
es.

GRANT ALL PRIVILEGES ON DATABASE eurotechgroup TO ethgrp;
GRANT SELECT ON mytable TO guest;

Mostra gli utenti di postgresql

db=#SELECT * from pg_user;

Mostra gli utenti connessi a postgresql

db=#SELECT usesysid, usename FROM pg_stat_activity;

Creare uno user di tipo read-only in un database postgresql

Database Crea/Delete

Creare un nuovo database dal terminale interattivo

db=# CREATE DATABASE newdb WITH OWNER postgres;

Creare un nuovo database dalla shell

su – postgres
/usr/local/pgsql/bin/createdb newdb -O postgres

Cancella un database

DROP DATABASE mydb;

Cancellare un database dalla shell

su – postgres
dropdb dbname

Se si utilizzano maiuscole nel nome del database in psql usare le ” ”  es.

DROP DATABASE myDB

BACKUP / RESTORE

Formato sql  per il backup 

Se si utilizza  il formato sql per effettuare il backup si possono usare i seguetni comandi per il backup restore

backup:
pg_dump -U user -h host db_dacopiare -p port -f dumpfile.sql
 $ pg_dump -U postgres -h 10.0.1.10 -p 5432 ethgroup2008 -f ethgroup2008.sql
restore:
 $ psql -U postgres -h 192.168.1.10 -W -d ethgroup2008 -f ethgroup2008.sql

Backup database in un server e ripristino in un altro server

per evitare username e password portarsi ad esempio sull’utente postgres,  il database deve essere presente nella macchina target diversamente va creato.

su - postgres

# creare il database nella macchina target  da terminale con un comando del tipo

createdb dbname

# esempi di comando per il trasferimento

pg_dump -h host_source dbname | psql -h host_target dbname
pg_dump dbname | psql -h host_target -d dbname
pg_dump -h host1 dbname -t mytable | psql -h host2 -d dbname
pg_dump -h host1 -U username -W dbname -t mytable | psql -h host2 -d dbname

Questi comandi possono essere efficacemente utilizzati specialmente su Tunnel ssh Tunnel via ssh per gestire il server postgresql anche con pgAdmin

Va considerato che nel server target sarebbe opportuno fossero già presenti gli utenti presenti nel server sorgente.

Backup di una tabella

# pg_dump --table catalogs -U tmpuser eurotechdb -f catalogs.sql

Ripristino di una tabella

psql -f catalogs.sql catalogs

Formato backup custom

backup:
pg_dump -Fc -U postgres -W ethgroup2008 -f ethgroup2008.backup

Restore:
pg_restore -h 10.0.1.10 -U postgres -c -W -d ethgroup2008 ethgroup2008.backup
  • -Fc formato compresso
  • -c drop database object prima di ricreareli
  • -C crea il database target
  • -f file output
  • -W password
  • -t = tabella
  • -a solo dati

Backup di tutti i database

pg_dumpall > alldb.sql
su – postgres
psql < alldb.sql

Visualizza i datatypes base

SELECT typname,typlen from pg_type where typtype='b';

Transazioni

Avviare una transazione

# BEGIN

rollback o commit a transazione

# ROLLBACK
# COMMIT

QUERY

Ovviamente la console interattiva consente di eseguire query dei vari tipi: select, update, insert …
In genere pero è preferibile passare per strumenti come pgadmin3 …
Di seguito alcune query di tipo generale

per cambiare database

\c biblioteca;

Display il piano di esecuzione di una query

EXPLAIN SELECT * FROM products;

Mostra il piano eseguendo la query sul lato server

EXPLAIN ANALYZE SELECT * FROM products;

EXPLAIN ANALYZE SELECT * FROM _getcatalog(300);

Query select per contare il numero di records in un tabella

per cambia database

\c     ethgroup2010U ;

quindi

select count(*) from catalogs;

Query di tipo count che ritorna il numero di records in un tabella data una spcifica colonna con valore diverso da null

select count(productline) from catalogs;

Aggiungere un contatore di record in una select

SELECT row_number() OVER() as rownum, table.* FROM table;

Conta il numero distinto di records data una spcifica colonna con valore diverso da null

select count(distinct productline) from catalogs;

Ottieni il minimo (MIN) o il massimo (MAX) valore di una colonna

# select MIN(ordercode) FROM products;

Ottieni il secondo

# SELECT MIN(ordercode) FROM products where ordercode > ( select MIN(ordercode) from productse );
# SELECT MAX(ordercode) FROM products where ordercode <( select MAX(ordercode) from productse );

Per inviare l’output di query select ad un file

# \o myoutput_file
# SELECT * FROM news;

ritorna l’output allo stdout
# \o

Ritorna dimensioni dei database e delle tabelle

Calcola le dimensioni di uno specifico database

SELECT pg_database_size(‘mydb’);

pg_size_pretty ritorna le dimensione in MB un formato più amichevole

SELECT pg_size_pretty(pg_database_size(‘mydb’));

pg_size_pretty ritorna le dimensione in MB un formato più amichevole

SELECT pg_size_pretty(pg_total_relation_size(‘big_table’));

Trova la tabella più grande del database selezionato

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

relpages: numero di pagine ( di default una pagina è 8Kb)
pg_class: system table, mantiene i dettagli delle relazioni

per ritornare l’ip del server postgres
SELECT * FROM inet_server_addr()

Memorizza la password dopo crittografia

SELECT crypt ( ‘erim’, gen_salt(‘md5’) );

PostgreSQL registra tutti i comandi eseguiti in un file chiamato ~/.psql_history

# cat ~/.psql_history

Stampa con EOG di Ubuntu

Mi è capitato di dover perdere delle ore per una stampa di un immagina da Ubuntu 14.04 con il visualizzatore di immagini (Eye of Gnome)

I driver della stampante era installato correttamente tantoché, aperto con GIMP, ha stampato correttamente.

La stampante è una Aficio MP C2500, ho seguito passo passo la guida http://www.openprinting.org/printer/Ricoh/Ricoh-Aficio_MP_C2500   molto dettagliata e precisa.

Questo è, attualmente, l’elenco delle stampanti

Printer list

Have a lot of fun!