PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...

learn more… | top users | synonyms (1)

4
votes
2answers
955 views

Have Postgresql accept 1 and 0 as true and false for boolean

I have a large file of dumped sql data to load in to postgresl (600mb+). In that dump, fields that are boolean true and false are expessed as 1 and 0 without quotes. Trying to load them in to ...
2
votes
1answer
469 views

Why does Postgres generate an already used PK value?

I'm using Django, and every once in a while I get this error: IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey" DETAIL: Key (id)=(1) already exists. My database ...
1
vote
1answer
92 views

Postgres WAL logs— point in time recovery with (time) gaps in the logs?

I'm setting up continuous archiving/ point in time recovery on Postgres and I've noticed some odd behavior. Here's the sequence of events: Save a base backup Archive WAL logs during normal DB ...
0
votes
1answer
42 views

Postgresql: No data left on drive, how can I wipe it and start over?

I have a system with a non-critical postgres database that was unmonitored and consumed all available space on its drive. The OS is unaffected so I am free to delete files. I would like to just ...
7
votes
2answers
2k views

How to do incremental/differential backup every hour in Postgres 9.1?

Trying to do an hourly hot incremental backup of a single postgres server. I have the following setup in postgresql.conf: max_wal_senders=2 wal_level=archive archive_mode=on archive_command='copy ...
4
votes
1answer
956 views

How to properly index hstore tags column to faster search for keys

I imported a large area of OpenStreetMap's planet.osm file into a postgresql database. The database contains a table called nodes. Each node has a geometry column called geom and a hstore column ...
-1
votes
2answers
164 views

Find records in same table with different company id but some other same values [closed]

I have subscriptions table that looks roughly: table subscriptions id (auto generated id) company_id city state subscription_date I would like to find records that: A) are of different company ...
3
votes
1answer
105 views

Streaming replication setup, sane setup?

Not being a DBA as main profession, I would like to know if the following assumptions are true. I have been searching the net for days and I think I have understand it correctly, but I would like to ...
2
votes
2answers
2k views

TOAST Table Growth Out of Control - FULLVAC Does Nothing

Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order to take advantage of autovacuum features and be in line with 30ish other PGSQL servers. This was done by a separate IT group who ...
1
vote
3answers
1k views

Bulk update of all columns

I am wondering if PostgreSQL has an update query somewhat like their insert values syntax. I have an updated set of data in this form currently: INSERT INTO ...
-1
votes
1answer
91 views

PostgreSQL select optimization

Can you please explain, how I can optimize select like this: SELECT * FROM calls WHERE id_temp % 5 = 0 LIMIT 300000 This select is working on table with 50 millions of records. Thanks a lot and ...
0
votes
1answer
162 views

Get old database off external hard drive

My hard drive crashed yesterday, luckily I had a back up. I didn't restore from backup though cause I needed a fresh system anyways. However there was a development database I would like back. Is ...
1
vote
3answers
602 views

What are the valid formats of a PostgreSQL schema name?

I can't seem to find documentation that describes the valid formats of a PostgreSQL schema name. I know that a schema name cannot: start with a number have spaces start with pg_ What else? Where ...
-1
votes
0answers
581 views

Can't connect to my Postgres databases [closed]

*** THIRD AND LAST UPDATE *** Excuse me very much, the error was highly stupid: I modified start.conf and changed it to manual. I thought that it was related to the start of the service at init, ...
5
votes
1answer
328 views

PostgreSQL: DBLink weird permission/connection error

The goal is to (1) create a connection, (2) return a view of another database, and (3) close the same connection all to be stored in a view (no stored procedure or embedded password). I came up with ...
0
votes
1answer
67 views

SQL Server fsync Options

PostgreSQL has the option to turn off fsync and synchronous commits (http://www.postgresql.org/docs/9.1/static/runtime-config-wal.html). Does SQL Server has this option somewhere?
7
votes
2answers
688 views

Convert units of measurement

Looking to calculate the most suitable unit of measurement for a list of substances where the substances are given in differing (but compatible) unit volumes. Unit Conversion Table The unit ...
0
votes
3answers
279 views

PostgreSQL not starting correctly, is it repairing itself first?

I never saw that problem before. I had problems and many PostgreSQL processes were stuck so I killed them with a -KILL... When I tried to restart, it says that it cannot restart, but the daemon ...
0
votes
1answer
54 views

Wrong return results

I'm trying to grab all the rows that have a risk of critical or high, with the discription or synopsis or solution or cve like password. But it keeps showing all rows not just rows with a risk of ...
0
votes
2answers
215 views

Is it possible to build an UNDO framework for postgres?

I was thinking of a table which would automatically log all transactions made to other tables and the command to undo that modifications. So every time you issue an UNDO() command you would read the ...
0
votes
0answers
192 views

phpPgAdmin does not see databases

When I go to databases in phpPgAdmin it says that there are 0 databases. Yet, if I run SELECT datname FROM pg_database it displays all databases OK. I would like to be able to 'explore' tables and ...
5
votes
3answers
2k views

Postgres Write Performance on Intel S3700 SSD

I'm not seeing the Postgres write performance increases I thought I would with a single SSD vs a hardware RAID 10 array of (16) 15k RPM SAS drives. I have a Dell R820 with a PERC H700 hardware RAID ...
0
votes
1answer
144 views

pg_upgrade fails with lc_ctype cluster values do not match

I'm upgrading my PostgreSQL version 9.1.4 database to version 9.2.4. Both the old and the new version are the bundled versions of postgresapp.com for Mac OS X. When trying to upgrade the database I ...
1
vote
1answer
667 views

Why is Postgres on 64-bit CentOS 6 significantly slower than Postgres on 32-bit CentOS 6

We have some Postgres + PostGIS applications that run well on CentOS 6 32-bit machines. We've recently been testing them on CentOS 6 64-bit machines, with similar configuration (all our machines are ...
1
vote
1answer
311 views

How can I speed up PgAdmin III Queries?

I've been noticing that PgAdmin III isn't as responsive as I expected it to be. For instance a query to select * ... from a table containing 20,397 rows takes 19.531 seconds in PgAdmin III and 7.383 ...
1
vote
1answer
646 views

Check if postgresql database is exists (case insensitive way)

Is there a "elegant built-in" case-insensitive way to check if db is exists? I've found only SELECT datname FROM pg_catalog.pg_database WHERE datname='dbname', but this is a CS check. The first thing ...
0
votes
1answer
289 views

Postgresql table with one integer column, sorted index, with duplicate primary key

I want to have a table in PostgreSQL with these properties: One column with an integer Allow duplicates in this column. Duplicates will be rare. Duplicates have no meaning logically and will be ...
2
votes
1answer
297 views

Cannot connect to server. Too many symbolic links. Postgres Error [closed]

I get this error when I try connecting to a PostgreSQL server: psql: could not connect to server: Too many levels of symbolic links Is the server running locally and accepting connections on ...
4
votes
1answer
244 views

Why aren't my Postgres WAL segments (up to the point of turning on archiving) being archived?

The pg_xlog directory for my master cluster looks like this: -rw------- 1 postgres postgres 16M Jun 21 21:42 000000010000000000000001 -rw------- 1 postgres postgres 16M Jun 21 22:42 ...
3
votes
4answers
226 views

Changing the representation of NULLs in pg_dump plaintext output

I'm trying to convert a large-ish Postgres DB (500 GB) to SQL Server 2012. After investigating a few third-party tools and being disappointed in features, performance, or both, I started pursuing a ...
1
vote
1answer
277 views

How does Postgres know which logs have been archived?

I'm asking this because Postgres was hard-stopped yesterday, and I fear that there could be partial / corrupt data in one of my archived log segments. I'd like to simply delete the logs from my slave ...
1
vote
2answers
575 views

Indexes Size 20 times bigger then the table itself and very slow queries

The table I want to query has only about ~3000 rows with 3 columns: int/varchar(100)/varchar(100) yet it takes ~30 sec to query them all! Then I saw that the table size is 1331MB but the indexes ...
1
vote
2answers
234 views

Query for master in Postgres replication

I'm trying to find out if there is a way to query for the master, from a slave PostgreSQL server that has been set up with server replication. From a slave, I can: SELECT pg_is_in_recovery() And ...
0
votes
1answer
470 views

How to convert Postgres from 32 bit to 64 bit

I would like to convert from PG 32 bit to 64 bit. I am testing with pg_upgrade (in several scenarios) but I think it cannot do that. With pg_upgrade, I can upgrade PG version 8.5 to v.9 (both of ...
0
votes
3answers
2k views

trouble creating a database with postgreql [closed]

I just installed Postgresql on my Windows laptop. I am now trying to create my first database. I launched the Windows PowerShell and tried the following: PS C:\Users\Chris\Documents\test> & ...
1
vote
1answer
357 views

Issues starting Postgres server [closed]

I'm trying to go through the book "7 Databases in 7 Weeks" and I'm completely stuck at starting a Postgres server. My current issue is that when I run pg_ctl start I get the following error: LOG: ...
0
votes
0answers
28 views

Calculating the row size of a table [duplicate]

Is there a way to calculate the row size (in bytes) of a particular table in postgresql / pgadmin3?
1
vote
1answer
282 views

Using PL/R for clustering geometries

I have a Postgres/PostGIS database of geography points which I'm trying to cluster; meaning I want to group and count them by proximity. I have had first success with the kmeans Postgresql extension, ...
1
vote
2answers
171 views

Postgres ODBC driver versions

On the Postgres ODBC driver page the name for each file is of the form: psqlodbc_09_01_0100-x64.zip psqlodbc_09_01_0100.zip psqlodbc_09_01_0100-1.zip psqlodbc_09_01_0200-x64.zip ...
0
votes
0answers
1k views

Cannot run pgAdmin from the terminal: No such file libwx_gtk2u

(Probably irrelevant) I'm using Puppet to setup/manage a class of computers that are equipped with PostgreSQL and PostGIS, and this implies that each computer goes through an unattended install: ...
2
votes
1answer
271 views

pgAdmin to display multiple result sets

With MS SQL Query Browser I can run multiple queries at the same time and each result set will display in it's own window. Does PostgreSQL/pgAdmin have this functionality? Plugin perhaps?
2
votes
1answer
1k views

To select from all tables inside the schema in PostgreSQL

Is it possible to select from all tables inside the schema? I got all the table names from select table_name from information_schema.tables but I am unable to use it to do my query.
0
votes
1answer
269 views

Is it possible for heroku postgres follower database to follow non-heroku postgres master?

I am considering to move our site's database from our dedicated db server to heroku postgres. as we have a few gigs of data in the db and don't want to take it down for a long migration downtime it ...
1
vote
2answers
1k views

How to set password for PostgreSQL database with a script?

In a bash script I'm writing, I need to create a database and set a password that is generated automatically by my script. The problem is I can't find a way to set up the password without user ...
1
vote
1answer
1k views

How much time will a vacuum/autovacuum operation take?

I manage a big (some hundreds of gigs) database containing tables with various roles, some of them holding millions of records. Some tables only receive large number of inserts and deletes, some other ...
1
vote
1answer
130 views

Should my Postgres backup server be as beefy (IO, processor, RAM) as my master?

I've set up a Postgres database on a server with 2 CPU cores, 4GB of RAM and an SSD volume (on top of RAID 10 of course). This is our (sort-of) "beefy" production database server (I can easily add ...
0
votes
1answer
71 views

postgresql backup and restore

I'm trying to backup the database I made in postgresql but it doesn't seem to work root@localhost:/home/tests# sudo -s -u postgres postgres@localhost:/home/tests$ psql psql (8.4.17) Type "help" for ...
2
votes
2answers
8k views

Forgotten PostgreSQL Windows password

This morning I’ve been trying to connect the Postgresql database on my Windows 7 professional desktop. The default value is ‘postgres’, but sure enough I forgot what password I used when I originally ...
0
votes
2answers
431 views

How to drop inheritance?

I am new to PostgreSQL. The situation I have is someone created a child table inherits from the parent table. And dropped the child table. But the parent table still has the "down arrow" sign on it. ...
0
votes
1answer
2k views

How to retrieve data from Postgresql index using java

I am writing an application for searching, using text search of Postgresql. I am a beginner in Database. I created a Gin index and i am trying to get data from it. My code is import ...