logobossnation

Chapter 4. Using pgAdmin pgAdmin (a.k.a. PgAdmin III or pgAdmin3) is the current rendition of the most commonly used graphical administration tool for PostgreSQL. Though it has its shortcomings, we are always encouraged by not only how quickly bugs are fixed, but also how quickly new features are added. Since it’s accepted as the official graphical administration tool for PostgreSQL, and packaged with many binary distributions of PostgreSQL, pgAdmin has the responsibility to always be kept in sync with the latest PostgreSQL releases. Should a new release of PostgreSQL induct new features, you can count on the latest pgAdmin to let you manage it. If you’re new to PostgreSQL, you should definitely start with pgAdmin before exploring other tools that could cost money.

1. Install Pgadmin On Ubuntu
2. Windows Installation Wizard

We should also mention that as of yet, we have not encountered a tool that’s absolutely superior to pgAdmin. Graphical EXPLAIN plan for your queries.

This most awesome feature offers a pictorial insight into what the query planner is thinking. Gone are the days of trying to wade through the verbosity of text-based planner outputs. PgAdmin ultimately interacts with PostgreSQL via SQL; it’s not shy about letting you see the generated SQL. When you use the graphical interface to make changes to your database, the underlying SQL to perform the tasks automatically displays in the SQL pane. For SQL novices, studying the generated SQL is a great learning opportunity. For pros, taking advantage of the generated SQL is a great time-saver. Direct editing of configuration files such as postgresql.conf and pghba.conf.

You no longer need to dig around for the files and use another editor. Data export. PgAdmin can easily export query results as CSV or other delimited format. It can even export as HTML, providing you with a turn-key reporting engine, albeit a bit crude. Backup and restore wizard.

Can’t remember the myriad of commands and switches to perform a backup or restore using pgrestore and pgdump? PgAdmin has a nice interface that’ll let you selectively back up and restore databases, schemas, single tables, and globals, and the message tab shows you the command line pgdump or pgrestore it used to do it. Grant Wizard. This time-saver will allow you to change permissions on many database objects in one fell swoop.

pgScript engine. This is a quick and dirty way to run scripts that don’t have to complete as a transaction. With this you can run loops and so forth that commit on each SQL update, unlike stored functions that require all steps completed before the work is committed. Unfortunately, you can not use it outside of pgAdmin GUI. Plugin architecture. Newly developed add-ons are quickly accessible with a single mouse-click.

You can even install your own. We have a description of this feature in. pgAgent plugin. We’ll be devoting an entire section to this cross-platform job scheduling agent which is similar in flavor to SQL Server’s job scheduler (SQLAgent). PgAdmin provides a cool interface to it. Figure 4-2. Hide or unhide database objects in pgAdmin browse tree If you check the Show System Objects in the treeview check box, you’ll see the guts of PostgreSQL consisting of internal functions, system tables, hidden columns in each table, and so forth.

You will also see the metadata stored in the informationschema catalog and the pgcatalog PostgreSQL system catalog. Informationschema is an ANSI-SQL standard catalog found in other databases such as MySQL and SQL Server. You may recognize some of the tables and columns from working with other databases and its superb for getting standard metadata in a cross database compatible way. Accessing psql from pgAdmin Although pgAdmin is a great tool, there are cases where psql does a better job.

One of those cases is executing large SQL files such as those output by pgdump and other dump tools. To do this, you’ll want to use psql covered in. PgAdmin has a feature that makes jumping to psql easy and painless. If you click on the plugin menu item as shown in and then psql, this will open a psql session connected to the database you are currently connected to in pgAdmin. You can use the cd and i psql commands to cd and run a psql script file. Figure 4-4. PgAdmin configuration file editor If the menu is greyed out and you are connected to a PostgreSQL server, then you don’t have the admin pack installed on that server or are not logged in as a superuser. To install the admin pack on a 9.0 or lower server, connect to the database named postgres as a superuser and run the file share contrib adminpack.sql.

For PostgreSQL 9.1 or above, connect to the database named postgres and run the SQL statement CREATE EXTENSION adminpack;, or use the graphical interface for installing extensions as shown in. Disconnect from the server and reconnect, and you should see the menu enabled. Permission Management For setting permissions on existing objects, nothing beats the pgAdmin Grant Wizard, which you can access from the Tools →Grant Wizard menu of pgAdmin. As with many other features, this option is greyed out unless you are connected to a database. It’s also sensitive to the location in the tree you are on. For example, to set permissions in objects located in the census schema, we select the census and then choose the Grant Wizard.

The grant wizard screen is shown in. You can then selectively check all or some of the objects and switch to the Privileges tab to define the roles and permissions you want to grant. Backup and Restore Most of the backup and restore features of pgdump and pgrestore are accessible from pgAdmin. In this section, we’ll repeat some of the examples we covered in and, but using pgAdmin’s graphical interface instead of the command line.

The backup and restore in pgAdmin are just GUIs to the underlying pgdump and pgrestore utilities. If you have several versions of PostgreSQL or pgAdmin installed on your computer, it’s a good idea to make sure that the pgAdmin version is using utilities versions that you expect. Check what the bin setting in pgAdmin is pointing to in order to ensure it’s the latest available, as shown in.

Warning If your server is remote or your databases are huge, we recommend using the command-line tools for backup and restore instead of pgAdmin to avoid adding another layer of complexity in what could already be a pretty lengthy process. Also keep in mind that if you do a compressed/tar/directory backup with a newer version of pgdump, then you also need to use the same or higher version of pgrestore because a newer pgdump compressed or tar backup can not be restored with an older pgrestore. Backing up of System Wide Objects pgAdmin provides a graphical interface to pgdumpall for backing up system objects, which does much the same as what we covered in. To use, first connect to the server you want to backup from the Server tree and then from the top menu, choose Tools → Backup Globals. Unfortunately, pgAdmin doesn’t give you any options of what to backup as you get by using the command line interface, and instead will backup all table spaces and roles. If you want to backup the whole server, doing a pgdumpall, then use the Tools → Backup Server option. PgScript pgScript is a built-in scripting tool in pgAdmin.

It’s most useful for being able to run repetitive SQL tasks. Unlike PostgreSQL stored functions, pgScript commits data right away which makes it particularly handy for memory-hungry processes that you don’t need completed as a single transaction. You can see an example of where we use it for batch geocoding here. The underlying language is lazily typed and supports loops, data generators, macro replacement, basic print statements and record variables. The general syntax is similar to that of Transact SQL—the stored procedure language of Microsoft SQL Server.

You launch pgScript by opening up a query window, typing in some pgScript specific syntax, and then click on the icon to execute it. We’ll show you some examples. Demonstrates how to use pgScript record variables and loops to build a cross tab table using the lufacttypes table we create in. It creates an empty table called census.hisppop with numeric columns of hispanicorlatino, whitealone, blackorafricanamericanalone, and so on. DECLARE @I, @labels, @tdef; SET @I = 0; labels becomes a record variable SET @labels = SELECT quoteident(replace(replace(lower(COALESCE(factsubcats4, factsubcats3)), ' ', '),':',')) As colname, facttypeid FROM census.lufacttypes WHERE category = 'Population' AND factsubcats3 ILIKE 'Hispanic or Latino%' ORDER BY shortname; SET @tdef = 'census.hisppop(tractid varchar(11) PRIMARY KEY '; Loop thru records using LINES function WHILE @I.

DECLARE @I, @labels, @tload, @tcols, @facttypes; SET @I = 0; SET @labels = SELECT quoteident(replace( replace( lower( COALESCE(factsubcats4, factsubcats3)), ' ', '),':',')) As colname, facttypeid FROM census.lufacttypes WHERE category = 'Population' AND factsubcats3 ILIKE 'Hispanic or Latino%' ORDER BY shortname; SET @tload = 'tractid'; SET @tcols = 'tractid'; SET @facttypes = '-1'; WHILE @I. Graphical Explain One of the great gems in pgAdmin is its informative, at-a-glance graphical explain of the query plan. You can access the graphical explain plan by opening up an SQL query window, write some query and then clicking on the icon. If we run the query: SELECT left(tractid, 5) As countycode, SUM(hispanicorlatino) As tot, SUM(whitealone) As totwhite, SUM(COALESCE(hispanicorlatino,0) - COALESCE(whitealone,0)) AS nonwhite FROM census.hisppop GROUP BY countycode ORDER BY countycode; We will get a graphical explain as shown in. The best tip we can give for reading the graphical explain plan is to follow the fatter arrows. The fatter the arrow, the more time consuming a step.

Figure 4-13. Graphical explain example Graphical explain will be disabled if Query →Explain →Buffers is enabled. So make sure to uncheck buffers before trying a graphical explain.

In addition to the graphical explain, the Data Outputcode tab will show the textual explain plan which for this example looks like: GroupAggregate (cost=111.29.151.93 rows=1478 width=20) Output: ('left'((tractid)::text, 5)), sum(hispanicorlatino), sum(whitealone). Sort (cost=111.29.114.98 rows=1478 width=20) Output: tractid, hispanicorlatino, whitealone, ('left'((tractid)::text, 5)) Sort Key: ('left'((tractid)::text, 5)) - Seq Scan on census.hisppop (cost=0.00.33.48 rows=1478 width=20) Output: tractid, hispanicorlatino, whitealone, 'left'((tractid)::text, 5). Job Scheduling with pgAgent pgAgent is a handy utility for scheduling jobs. Since pgAgent can execute batch scripts in the OS, we use it for much more than scheduling PostgreSQL jobs. In fact, we don’t recall the last time where we even touched crontab or the Windows task scheduler. PgAgent goes further, you can actually schedule jobs on any other server regardless of operating system. All you have to do is install the pgAgent service, PostgreSQL server itself is not required but the client connection libraries are.

Since pgAgent is built atop of PostgreSQL, we are blessed with the added advantage of having access to all the tables controlling the agent. If we ever need to replicate a complicated job multiple times, we can just go into the database tables directly and insert records instead of using the interface to set up each new job. We’ll get you started with pgAgent in this section, but please visit to see more working examples and details of how to set it up. Scheduling Jobs Each scheduled job has two parts: the execution steps and the schedule to run. When you create a new job, you need to specify one or more steps.

For each step, you can enter SQL to run, point to a shell script on the OS, or even cut and paste in a full shell script as we commonly do. The syntax for the SQL will not vary across OS and the PostgreSQL server it runs on is controlled by the Connection Type property of the step. The syntax for batch jobs should be specific to the OS running it. For example, if your pgAgent job agent is running on Windows, your batch jobs should have valid DOS commands. If you are on Linux, your batch jobs should have valid sh or bash commands. Steps run in alphabetical order and you can decide what kind of actions you wish to take upon success or failure of each individual step. You also have the option of disabling steps that should remain dormant but you don’t want to delete because you may reactivate them later.

Once you have the steps ready, go ahead and set up a schedule to run them. You can get fairly detailed with the scheduling screen. You can even set up multiple schedules. By default, all job agents on other machines will execute all the jobs. If you want to only have the job run on one specific machine, you’ll need to fill in the host agent field when creating the job. Agents running on other servers will skip the job if it doesn’t match their host name. Note pgAgent really consists of two parts: the data defining the jobs and storing the job logging, which resides in pgAgent schema, usually in postgres database; the job agents query the jobs for the next job to run and then insert relevant logging information in the database.

Generally, both the PostgreSQL Server holding the data and the job agent executing the jobs reside on the same server, but in practice they are not required to. Additionally, you can have one PostgreSQL server servicing many job agents residing on different servers. A fully formed job is shown in. SELECT c.relname As tablename, d.description FROM pgclass As c INNER JOIN pgnamespace n ON n.oid = c.relnamespace INNER JOIN pgdescription As d ON d.objoid = c.oid AND d.objsubid = 0 WHERE n.nspname = 'pgagent' ORDER BY c.relname; tablename description -+- pgajob Job main entry pgajobagent Active job agents pgajobclass Job classification pgajoblog Job run logs.

Pgajobstep Job step to be executed pgajobsteplog Job step run logs. Pgaschedule Job schedule exceptions As you can see, with your finely-honed SQL skills you can easily replicate jobs, delete jobs, edit jobs directly by messing with pgAgent packaged tables. Just be careful! Although pgAdmin provides an intuitive interface to pgAgent scheduling and logging, you may find the need to run your own reports against the system tables. This is especially true if you have many jobs or you just want to do stats on your results.

We’ll demonstrate the one query we use often. SELECT j.jobname, s.jstname, l.jslstart,l.jslduration, l.jsloutput FROM pgagent.pgajobsteplog As l INNER JOIN pgagent.pgajobstep As s ON s.jstid = l.jsljstid INNER JOIN pgagent.pgajob As j ON j.jobid = s.jstjobid WHERE jslstart CURRENTDATE ORDER BY j.jobname, s.jstname, l.jslstart DESC; We find it very useful for monitoring batch jobs because sometimes these show as having succeeded when they actually failed. PgAgent really can’t discern success or failure of a shell script on several operating systems. The jsloutput field provides the shell output, which usually details about what went wrong.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

PostgreSQL & PostGIS In this chapter we will see how to set up PostgreSQL on Windows and how to create a database in which you can store geographic data. We’ll be using the open source GIS software QGIS in this chapter, so it will be helpful if you are already famliiar with it.

In the following chapter, we will see how to import OpenStreetMap data into a PostgreSQL database. Installing PostgreSQL and PostGIS In this section we will install PostgreSQL and then add the PostGIS spatial extensions. This is fairly easy to setup using the One-Click Installer.

Navigate your web browser to the PostgreSQL website and find the download page here:. From here you can find installation instructions for different operating systems. Click on the “Windows” link.

This page explains what the One-Click Installer will do. It will install three different components:. PostgreSQL server: The database software, the core component.

Install Pgadmin On Ubuntu

pgAdmin III: The graphical interface for managing your databases. StackBuilder: A tool for adding additional applications; we will use this for adding the PostGIS extensions. Click on Download. You will see several different Installer options for different versions of the PostgreSQL software. Download the most recent version. As of this writing it is version 9.3.1. Click on the button that says Win x86-32.

This is the installer for the 32-bit version of Windows. When it has finished downloading, run the One-Click Installer. Click “Next” to navigate through the installation wizard. The default options should be fine. You will need to provide a password for the first database user (the user is postgres).

This user has superuser privileges, meaning that they can do whatever they want, so don’t forget the password that you use! You can create as many databases as you want using Postgresql. You might want a database for your geographic data, and separate databases for other projects that you are working on. And you may want different people to have different types of access to these databases. For this purpose, every database that you create uses the concept of.users. and.roles.

A database must always be owned by a user, and usually that user will need a password in order to make changes to the database. Additional users can be given permission to access a database, and they can be given certain roles. For example, you may want a database user that can only read information from the database, but cannot change it. Or you may want a user that can add data, but does not have permission to delete it.

With users and roles, this is possible. For now we won’t worry too much about this, just remember that your database is owned by a.user., and to access the database you will need the user’s name and password. The first user we create (named postgres) is a.superuser., meaning they have permission to do everything with the databases.

After you have clicked through the wizard and accepted the default configuration options, the wizard will install everything for you. It may take a few minutes. When the installation is complete, the wizard will ask you if you want to launch StackBuilder, which is the utility that will allow us to install PostGIS.

Make sure the box is checked before you click “Finish.”. Now we’ve successfully installed PostgreSQL and we need to add the PostGIS extensions.

When the StackBuilder wizard opens, select your PostgresSQL installation from the dropdown menu and click Next. It will look something like this:. Open the “Spatial Extensions” tab and check the box next to PostGIS. As of this writing the most recent version of PostGIS is 2.1. Click Next to download the extensions and install. When prompted, click “I Agree” to accept the terms and conditions.

The PostGIS installer will ask more questions, but generally the default options are fine. You can tell it to create the first database automatically, but we will learn how to do that ourselves next. To begin the PostGIS installation you will need to supply the postgres password that you created when you installed PostgreSQL. If you are asked to register the GDALDATA environment variable, click “Yes.”. When the installation is completed, click “Close” and then “Finish.” Creating a Database Now that we have installed all of the necessary software, we will create a database.

Windows Installation Wizard

We will use pgAdmin III, which is a graphical database client that is useful for querying and modifying databases. PgAdmin III is the official client for PostgreSQL and lets you use the SQL language to manipulate your data tables.

It is also possible to create and manipulate databases from the command-line, but for now, pgAdmin III is an easy way to get started. Open pgAdmin III. It should be in the Start Menu under All Programs - PostgreSQL 9.3 pgAdmin III. In the panel on the left under Servers, right-click where it says PostgreSQL and click “Connect.”. Enter the postgres user password that you created when you installed the software.

Remember that the username and password are required so that you can create and access a database. Right-click on Databases and select New Database. You need to enter a few pieces of information to create the new database: name and owner. In the Properties tab, give the new database a name. In this example, we name our database gisdb. We should also give our database an owner.

Since we only have one user right now, let’s give our database the owner postgres. (Note: for security reasons it is usually a good idea to create users without superuser permission, but for now we won’t worry about this.). Click OK to create the database. You will now see your database listed under “Databases.”. We need to run a command now to enable the database with PostGIS extensions. Click on the SQL button at the top of PgAdmin III. In the query window, type: CREATE EXTENSION postgis;.

Then click the “Execute query” button. Load Sample Data (optional) If you are comfortable so far and are familiar with QGIS, follow along as we load some data into our new database. To do this, we will use a utility that converts shapefiles and loads them into the database. Make sure that your new database is selected in the panel on the left and go to Plugins - PostGIS Shapefile and DBF loader 2.1. Download mp3 gratis selamat ulang tahun sayang. Click “Add File” and find a shapefile on your filesystem. If you don’t have any shapefiles, you can download a sample.

Once you have selected a file, click “Import.” If everything goes smoothly, the output will read “Shapefile import completed.”. Now let’s load the data from our database into the QGIS application. If you don’t have QGIS you can download it on the. Open QGIS and click on the “Add PostGIS Layers” button. Under “Connections” at the top, click “New.”. Give the new connection a name.

Under database type gisdb (or whatever you named your database). Enter the username postgres and your password below. Click OK to save the connection settings.

Then click “Connect” to connect to your PostgreSQL server. You may need to enter your username and password again. If everything is successful, you will see the shapefile layer (or multiple layers with different features types) that you loaded into the database available here.

Select a layer and click “Add” to add it to your map. When you add the layer you will need to select a coordinate system to display the data in. You will most likely want to select WGS 84, which is the coordinate system OpenStreetMap uses. Note that the layer behaves the same as if you had loaded a shapefile directly into QGIS. The only difference is that if you edit the layer, the changes will be saved in your database. Summary Now that you have seen how to set up PostgreSQL and PostGIS, as well as how to create a new database, you’re ready to try the utilities which allow us to import raw OSM data into a database. We’ll take a look at this in the.