vicg4rcia + postgresql   38

Basic PSQL commands | Manikandan's Weblog
PostgreSQL is an ORDBMS for managing data stored in relations. It offers many features such as complex queries, triggers, transactional integrity, multiversion concurrency control.
Also, PostgreSQL can be extended by the user in many ways, data types, functions,aggregate functions, index methods.
postgres  postgresql  programming  database 
december 2014 by vicg4rcia
Landsliding Into PostGIS With KML Files | Big Fast Blog
In this post I will show, in repeatable steps, how to install PostGIS, load in geospatial data found in a KML file and run queries against that data. The focus of this geospatial data will be landslides and our resulting database will allow us to query, using longitude and latitude co-ordinates, the landslide status of a specific geographical point. The returned status for the given co-ordinates will be “Mostly Landslides”, “Few Landslides”, “Flat Land”, “Not Mapped”.
databases  programming  PostgreSQL  kml  PostGIS  postgres 
february 2014 by vicg4rcia
New explain |
Long time ago I wrote small program to filter EXPLAIN ANALYZE output, and add summary of time. A bit later (I guess, I don't recall exact time line, it could have been earlier) Michael Glaesemann started – cool tool for checking what might be wrong with given plan. I'm not really happy with the emphasis Michael put on bad rowcount estimates, so I decided to write my own tool. Enter Basic idea is: paste your explain analyze plan, and see the output. You can click on column headers to let it know which parameter is the most important for you – exclusive node time, inclusive node time, or rowcount mis-estimate. It is definitely not perfect. I know of at least 1 bug now, and will fix it in not-distant future. But, as for now – you can test it, play it, or simply use it. If you'd like to change/fix something – sources are freely available. Just be warned – it's Perl ;-P
postgresql  sql  database  postgres 
february 2014 by vicg4rcia
Boundless : Introduction to PostGIS
A comprehensive tutorial on the Postgres GIS extension/addon
postgis  postgres  postgresql  geodata 
february 2014 by vicg4rcia
How do I enable remote access to my PostgreSQL server?
a quick how-to to enable full remote access (listening address and authentication) to a postgres server
postgres  postgresql  sysadmin  linux 
february 2014 by vicg4rcia
How to drop a PostgreSQL database if there are active connections to it? - Stack Overflow
I need to write a script that will drop a postgresql database. There may be a lot of connections to it but the script should ignore that. The standard DROP DATABASE db_name query doesn't work in cases when connections exist. How can I solve the problem?
postgresql  postgres  database  sql 
december 2013 by vicg4rcia
How to force-drop a postgresql database by killing off connection processes « The Gahooa Perspective
Ever need to drop a postgresql database, but it would not let you because there are open connections to it (from a webapp or whatever)? Quite annoying. If on a production server, and other databases are being used, restarting postgresql is a last resort, because it generates downtime for your site (even if small). I finally took the time to scratch around and find the answer.
postgresql  database  postgres 
december 2013 by vicg4rcia
PostgreSQL – How to change default schema
“public” is PostgreSQL default scheme, i have to change it because i had migrated a new database data into another new schema call “new_public”.
Before start to change, i have to check what is current PostgreSQL default schema?
december 2013 by vicg4rcia
Doctrine Known Vendor Issues
This section describes known compatability issues with all the supported database vendors:
doctrine  doctrine2  postgres  postgresql 
november 2013 by vicg4rcia
select * from depesz; » Blog Archive » How much RAM is PostgreSQL using?
(disclaimer: all the data and examples in here are on Linux – the same data can be probably obtained on other systems too, it's just that I work on Linux and don't know other systems well). This question pops occasionally in various places – PostgreSQL is using too much memory, why is that, and how can it be mitigated? Before we can go to “optimizing", we should understand the problem. But do we? Both standard tools – ps and top – lie. How/why? Let's see.
PostgreSQL  postgres  linux 
november 2013 by vicg4rcia
Replicating PostgreSQL with Bucardo - Gabriel Weinberg's Blog
If the title makes no sense to you, PostgreSQL is an open-source database. Replication means continuously copying changes from one database to another, e.g. for backup, scalability or high availability. And Bucardo is one of several pieces of software that help you achieve various forms of Postgres replication.
replication  bucardo  postgresql  postgres  backup 
november 2013 by vicg4rcia
PostgreSQL tips and tricks - Gabriel Weinberg's Blog
I've been using PostgreSQL (an open-source database) for many years. Here are some of the less obvious tips and tricks I've picked up. This post isn't meant to be a comprehensive tuning or scaling guide, though I link to some good documentation below.
postgresql  postgres 
november 2013 by vicg4rcia
pgdbsync - Project Hosting on Google Code
pgdbsync allows us to track the differences in the database structure between different databases. It also create the needed script to synchronize the servers and even to run the script.
postgresql  postgres  database 
october 2013 by vicg4rcia
PostgreSQL Tips and Tricks | gtuhl: startup technology
Here’s a dozen tips for working with a PostgreSQL database. It is a sophisticated and powerful piece of software and just knowing a few rules of thumb before diving in can be a huge help. If you want more detail read the amazing documention. My list of tips was very long so I just chopped off a dozen for this post.
postgresql  postgres 
october 2013 by vicg4rcia
Create PostgreSQL Table Partitioning (Part 1)
PostgreSQL supports partitioning via table inheritance. So the partitioning is made in such a way that every child table inherits single parent table. Parent table is empty and it exists just to describe the whole data set. PostgreSQL partitioning can be implemented in range partitioning or list partitioning.
postgresql  postgres 
october 2013 by vicg4rcia
Strange high CPU I/O wait on postgresql server
I'm really struggling with a performance issue currently, where all my database queries takes up to a 1000 times more than normally, when my database server is under "heavy" load. I have a server that runs ubuntu 12.04 and hosts my postgresql 9.2 database. The server is on and it is a EX6s (8 core xenon with 32Gb ram and two 3T HDD's in a RAID1 setup using ubuntus software raid). When I run a high volume of queries (which I often done in the night) then I see that almost all CPU usage is spend on CPU I/O wait. I have new relic monitoring installed, and I can't seem to find any other indications of what could be the root cause of this CPU I/O wait, which clearly is a bottleneck to my performance and throughput. So the question is, what is this CPU I/O wait and what is it waiting for?
postgresql  linux 
october 2013 by vicg4rcia
Logging Difficult Queries PostgreSQL wiki
If you want to find the queries that are taking the longest on your system, you can do that by setting log_min_duration_statement to a positive value representing how many milliseconds the query has to run before it's logged. In PostgreSQL 8.4+, you can use pg_stat_statements for this purpose as well, without needing an external utility.
postgresql  postgres 
july 2013 by vicg4rcia
PerformanceTips | libpqxx
If you've been working with databases a lot, chances are you already know how to deal with performance problems. But if you don't, here are some tips on identifying and fixing performance problems. Some of them will work for any SQL database, others are specific to postgres.
postgresql  postgres 
july 2013 by vicg4rcia
SQL | fmcgeough
Posts from the ‘SQL’ Category
postgresql  postgres 
july 2013 by vicg4rcia
PostgreSQL Stuff
Here's what this .psqlrc does:

Show date on startup
Set client encoding to SQL_ASCII rather than operating-system default (which would be UTF8 on Linux)
Always commit manually.
Be verbose about feedback (esp. handy for error messages)
Command-line prompt formatting: show the db username at the database hostname, at the database, at the port; show if we are in a transaction, and show either > or # at the prompt depending on whether or not our database user has admin-like powers.
Instead of displaying nulls as blank space, which look the same as empty strings (but are not the same!), show nulls as <
july 2013 by vicg4rcia
In the first chapter of the PostgreSQL PHP tutorial, we will provide necessary definitions. We will show, how to install PostgreSQL database and the required packages. The examples will be run on the command line using the PHP CLI.
postgresql  sql  php 
may 2013 by vicg4rcia
15 Practical PostgreSQL Database Administration Commands
Earlier we discussed about how to install PostgreSQL database on Linux from source. In this article, let us review top 15 practical postgreSQL DBA command examples. If you are a mySQL administrator, check-out our 15 examples of mysqladmin command article that we discussed a while back.
april 2013 by vicg4rcia
How to emulate 'insert ignore' on Postgres
To get the insert ignore logic you can do something like below. I found simply inserting from a select statement of literal values worked best, then you can mask out the duplicate keys with a NOT EXISTS clause. To get the update on duplicate logic I suspect a pl/pgsql loop would be necessary.
postgresql  database  sql 
march 2013 by vicg4rcia
Understanding Postgres Performance - Craig Kerstiens
For many application developers their database is a black box. Data goes in, comes back out and in between there developers hope its a pretty short time span. Without becoming a DBA there’s a few pieces of data that most application developers can easily grok which will help them understand if their database is performing adequately. This post will provide some quick tips that allow you to determine whether your database performance is slowing down your app, and if so what you can do about it.
january 2013 by vicg4rcia
More on Postgres Performance - Craig Kerstiens
I wrote about some of the great new features in Postgres 9.2 in the recent announcement on support of Postgres 9.2 on Heroku. One of those awesome features, is pg_stat_statements. Its not commonly known how much information Postgres keeps about your database (beyond the data of course), but in reality it keeps a great deal. Ranging from basic stuff like table size to cardinality of joins to distribution of indexes, and with pg_stat_statments it keeps a normalized record of when queries are run.
january 2013 by vicg4rcia
delicious robots blog
Warning: this post may contain profanity. I'm a programmer, I don't like dealing with sysadmin type things.

I love PostgreSQL, the baddest-ass relational database on the planet. For real.

Ugh, but it's super confusing to set up if you are used to just setting up MySQL users with passwords and then connecting to the database with said username and password you set up. Postgres has some odd defaults that make this less than straight forward.
linux  postgresql 
december 2012 by vicg4rcia
postgresql - What's the default superuser username/password for postgres after a new install? - Server Fault
I have just installed postgres 8.4 on Ubuntu 9.10 and it has never asked me to create a superuser. Is there a default superuser and its password? If not, how do I create a new one?
december 2012 by vicg4rcia
MySQL vs PostgreSQL - WikiVS
MySQL vs PostgreSQL is a decision many must make when approaching open-source relational databases management systems. Both are time-proven solutions that compete strongly with proprietary database software. MySQL has long been assumed to be the faster but less full-featured of the two database systems, while PostgreSQL was assumed to be a more densely featured database system often described as an open-source version of Oracle. MySQL has been popular among various software projects because of its speed and ease of use, while PostgreSQL has had a close following from developers who come from an Oracle or SQL Server background.
postgresql  mysql  postgres 
august 2012 by vicg4rcia

Copy this bookmark: