Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
6
votes
1answer
470 views

Troubleshooting Postgresql spatial query failure. Next steps?

We have a set of spatial queries that are failing and I'm struggling with troubleshooting them. I suspect that we're running into some bug, but I'd like to nail things down a bit better so as to be ...
7
votes
1answer
3k views

Getting SQLSTATE in psql error messages

I have the following problem. SELECT * FROM tgvbn(); ERROR: function tgvbn() does not exist LINE 1: SELECT * FROM tgvbn(); ^ HINT: No function matches the given name and ...
2
votes
1answer
2k views

Archive database on Postgres

I have a table with more than 60mio records, where every month I have to add about 2-4 milions. It's not insert only table, I need to read from it and update recently added rows (status changes, etc.)....
18
votes
1answer
10k views

Database archive solutions

In continuation to a question posted by me on Is it a good idea to move high-volume and highly-accessed tables to a separate database?, am looking out for different techniques/solutions available for ...
5
votes
1answer
6k views

How does postgres use the schema search path when making indexes?

Using Postgres 9.1.4 with PostGIS 1.5, as part of a Rails 3.2.x app. I have a DB containing schemas A and B, with schema A empty and schema B containing tables foo and bar, with indexes on each table ...
3
votes
1answer
361 views

Why does PostgreSQL aggregate result in the Index or Seq scan outputting all columns of the table?

Doing a query like this: SELECT fruit, avg(cost) FROM fruit_table GROUP BY fruit results in a query plan like this: Sort (cost=3713873.41..3713874.46 rows=420 width=16) Output: fruit, (avg(cost))...
1
vote
1answer
2k views

How to Restore Postgresql Cluster from data directory

in our office we have a development database server that runs Debian 5, on this server we had a postgresql 8.1 running but because of the lack of internet access from the server we backed up with ...
1
vote
0answers
149 views

Long disk queue when no traffic

I got dedicated disk (RAID 10 - 4 SAS disks) for Postgres. I am bit worried as recently, I see under Windows Resource Manager disk queues like 3+ in the mornings (low traffic hours), like every 30-60 ...
5
votes
3answers
3k views

Optimizing query using view on EAV structure

An application is writing into a database that follows an EAV structure, similar to this: CREATE TABLE item ( id INTEGER PRIMARY KEY, description TEXT ); CREATE TABLE item_attr ( item ...
3
votes
1answer
6k views

How can I create a dblink from Postgresql 8.3 to SQL Server (2000 and 2008)?

I have a Postgresql database that need to read data from two SQL Servers (one SQL Server 2000 and one SQL Server 2008). I want to setup two dblinks to the SQL Servers. After googling, foruming, ...
2
votes
2answers
2k views

migrate SQL Server geography data to PostGIS geometry data

I'm trying to use SSIS to transfer point data from a Geography column in SQL Server 2008 R2 to a geometry column in a Greenplum (Postgres 8.2.15) database with PostGIS. I know there's a way to do the ...
0
votes
1answer
3k views

pgAgent installation with Stack Builder in PostgreSQL [closed]

I have installed PostgreSQL 9.1 on ubuntu 12.04 64-bit. I try to run Aplication Stack Builder and install pgAgent. At first I am asked for root password, which I provide, after I choose PostgreSQL 9....
1
vote
1answer
1k views

Tutorial for installing and setting up 'streaming replication' with PostgreSQL on Windows XP

Please refer to the diagram in the link below. While looking at my goals. http://i.stack.imgur.com/GkVWb.png GOAL: Get PostgreSQL 9.1 running on 3 terminals on the 'on site' location. 'on site' ...
4
votes
1answer
431 views

Postgresql - Internal Array handling - array type or pointer type

I have a question, about the internal handling of array columns in Postgres. Does the engine handle it as a classical array with memory alloc or is it somehow a pointer list. It would be interesting ...
5
votes
1answer
2k views

PostgreSQL versus MySQL for EAV structures storage

Is there any practical difference or what are the advantages of using Postgres 9.x versus MySQL 5.x for storing EAV (Entity-Attribute-Value) structures, especially in terms of read performance?
4
votes
1answer
2k views

Tricky selection of grouped rows, selecting based on values of two distinct but related rows

Say I have a table with the following information (sorted here for display purposes) taskid | reference | centreid | date_out --------------------------------------------------- 10001 | '...
0
votes
1answer
1k views

What's included in the default OSX 10.8 install of Postgres?

I've seen several sites claim that Postgres is installed by default for OSX 10.7 and above and I see there's a psql command in /usr/bin/, but there doesn't seem to be a pg_ctl anywhere. So is the ...
3
votes
1answer
793 views

Select consecutive number of rows starting from some number

What I need is to select 100 consecutive rows from a table, starting at some defined point without ordering data. As far as I can observe, inserted data to a table is stored in no order but is ...
2
votes
1answer
480 views

Number of virtual tables that specified user is able to write queries in PostgreSQL

I have one question about PostgreSQL that I can't understand. As it seems so far, it needs some of bruteforce in tables. Well, I did that lots of time and I found nothing. So, firstly I will cover my ...
0
votes
1answer
447 views

Heroku PostgresApp - Fails to start with edited postgresql.conf

I am trying to tune my local pg running from the Heroku Postgress App. I am seeing strange errors in the OSX console whenever I change some of the internal settings: PM Postgres[78709]: Could not ...
2
votes
2answers
3k views

Postgresql after 'for each row' triggers don't actually execute after each row?

If I have a single sql statement that inserts or updates lots of rows and a db trigger that runs after each row is inserted or updated, it seems like the triggers run after all the rows are inserted ...
10
votes
3answers
3k views

Efficiently comparing prices in different currencies

I want to make it possible for user to search products within a price range. User should be able to use any currency (USD, EUR, GBP, JPY, ...), no matter what currency is set by the product. So, the ...
1
vote
0answers
1k views

PostgreSQL pg_bulkload query eats all free disk space

I have a simple task: bulk insert csv file to the table. The .ctl file is TYPE = CSV # Input file type ESCAPE = \ # Escape character for Quoting ...
14
votes
1answer
13k views

How to determine the sweet spot between pool size and database connections in PostgreSQL

We are having trouble handling the traffic during peak hours to our database server. We're looking into improving the hardware (see this question about that side of things) but we also want to work on ...
2
votes
1answer
1k views

Error creating index on PostgreSQL 9.1

I have a process where user A creates a table and user B tries to create an index on the new table, owned by A. Users A and B are from the same group but user B is an automated process. Is it ...
4
votes
1answer
1k views

High availability in postgresql

We will be switching from mysql cluster to either postgresql or oracle (with RAC). Mysql cluster is quite reliable, but lacks features in SQL that make programmers life easier (they consider it as ...
7
votes
2answers
2k views

SQL ANSI JOIN precedence

I have a query that looks like this: SELECT * FROM TBLA A LEFT JOIN TBLB B ON A.Col1 = B.Col2 RIGHT JOIN TBLC C ON B.Col3 = C.Col4 JOIN TBLD D ON C.Col5 = D.Col6 In which order will ...
3
votes
1answer
1k views

“Zero” downtime and minimal space requirements for upgrade to PostgreSQL 9.2

Need to migrate from PostgreSQL 8.3.x to 9.2 Constraint 1: 8.4 and above have made datetime format to be integer by default. Wasn't so in 8.3.x. As a result pg_upgrade can't be used. Unless server is ...
1
vote
1answer
2k views

PostgreSQL 9.1 - Asynchroneous commits performance boost

During peak hours our users experience a lot of timeouts due to an overloaded database server. We are currently trying to make optimizations in order to cope with the high traffic. We read in the ...
1
vote
1answer
2k views

Can't start postgres server, SHMMAX too small

When I try to start the postgres server with sudo /etc/init.d/postgresql start I get this error: Starting PostgreSQL 8.4 database server: mainThe PostgreSQL server failed to start. Please check ...
2
votes
1answer
444 views

postgresql pgadmin3 queryconnect attribute does not work

I'm using Fedora 17 and pgadmin3 to auto-connect to a postgresql database with one button click. Every time I want to run an SQL statement, I have to do 4 steps, (open pgadmin3), (connect to the ...
1
vote
2answers
648 views

How to remove tables and indexes?

I've a file with the SQL statements to create tables, indexes, etc, and another file with data test to use in the test database. Then, I want that they can be removed. How to remove table and indexes ...
2
votes
1answer
460 views

PostgreSQL asynchronous replication (and possible data loss)

I'm trying to wrap my head around Postgres' asynchronous replication. The way I'm reading the documentation, if the master goes down after data was committed, but before it's shipped to slaves, the ...
6
votes
1answer
4k views

Check what event called the trigger on PostgreSQL?

I'm trying to check what event called my trigger, like INSERT, UPDATE or DELETE. Oracle triggers can check this in a simple 'IF' statement: IF INSERTING, IF UPDATING or even IF DELETING Is there a ...
3
votes
1answer
172 views

query discrepancies on very large postgres table

I have a fairly large table (25m entries) which stores tracking data, I am not a postgres admin by trade so this may be a simple problem. these two queries although similar are taking massively ...
7
votes
2answers
1k views

PostgreSQL: Query DB for rows containing integer[] > or < than specified value

I have a table which stores historical temperatures for the past 5 days stored in an integer[] column based on city. The question is: How can I query for entries which have a value great than or less ...
1
vote
1answer
785 views

Search for null in id column over all tables programmatically

I want to automatically check for null ID's over all id columns in my database, I have to check all tables of which there is about 50,000 so manually doing this is infeasible
1
vote
1answer
488 views

Postgres replication/replication to server outside firewall

I am looking for a way to have a postgresql database (running server version 9.1) replicated. I want the mirroring to be able to have one server (the slave) standing outside our firewall. I have been ...
8
votes
2answers
3k views

How to wait for PostgreSQL to be startable / restorable?

I'm testing a PostgreSQL 8.2.1 to 9.2 upgrade on a virtual machine running a custom Linux distro. The upgrade procedure is as follows: Start the pg service Vacuum all DBs (not sure if this is needed) ...
3
votes
1answer
1k views

How to wait between PostgreSQL DB restore and reindexing?

I've written a script to upgrade PostgreSQL from 8.2.1 to 9.2.1 on a Linux From Scratch distribution: Start the pg service Vacuum all DBs (not sure if this is needed) Backup with pg_dumpall Stop the ...
2
votes
1answer
2k views

PostgreSQL - How can I manage WAL archiving for different databases on a server?

I'd like to use WAL archiving for incremental backup of my database. But I have 2 databases on my server, and I can't afford the disc space for backup of both of them. So I only want to backup one of ...
0
votes
1answer
416 views

Can't access Brew PostgreSQL

I've used Brew to install PostgreSQL. I want to log in with psql. Since I haven't set a password I 'm trying to login without one. When I try I get this: psql: fe_sendauth: no password supplied I ...
5
votes
1answer
723 views

Storing application settings with different datatypes

I'm developing a web application and I plan to store application settings in the database in name,value fields (one row = one setting). These settings range from directory paths to simple yes/no ...
3
votes
1answer
1k views

Model with variable number of “properties” of different types.

I would like to have a table of microscope slides, where on top of the basic id, name,date_created would each slide also have a number of editable "properties". Imagine one slide having ...
8
votes
2answers
1k views

High Availability for postgresql

I'm new in PostgreSQL database. Recently our developer needed to do some upgrades in our systems. Because of that we are planning to implement some method in order to implement database failover. ...
2
votes
1answer
3k views

Recommendations for PostgreSQL training [closed]

I'm looking for recommendations for PostgreSQL training (on-site, off-site, remote) that covers administration, performance optimization, and clustering solutions like Slony and pgpool. Finding ...
9
votes
2answers
965 views

PostgreSQL PITR restore

I have a PostgreSQL 9.1 server running on a CentOS 5.8 32bit OS and I have enabled WAL archiving. On this server, there are three databases: databaseA, databaseB and databaseC. Someone deletes a ...
4
votes
3answers
15k views

Copy PostgreSQL data from one PC to another

I am migrating my server application from the existing system to another system. Unfortunately, the existing system is also the database server. It has data stored. Is it possible to copy this data to ...
7
votes
1answer
13k views

Getting a random row from PostgreSQL?

I want to get a random row from my table by id. My table: ID|Word |Dificult|Category_id| 1 |'Dumb' |'Easy' | 3 | 2 |'Leopard'|'Medium'| 6 | If my user selects a category and ...
5
votes
2answers
3k views

How do I implement insert-if-not-found for transactions at serializable isolation level?

I'm having a hard time figuring out how to exactly implement a 'insert if not found' function. Consider the following. We have a table called artist with 2 columns, (name, id) where name is the ...