PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

5
votes
1answer
592 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
81 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
1k 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
474 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
60 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 ...
1
vote
2answers
426 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
337 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
4k 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
266 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
1k 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
718 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
2k 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
390 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
437 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
348 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
427 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
462 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
1k 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
828 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 ...
1
vote
1answer
894 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
3k 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
742 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
32 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?
2
votes
1answer
533 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
216 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
2k 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
653 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
2answers
3k 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
420 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 ...
2
votes
2answers
2k 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 ...
2
votes
1answer
3k 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
144 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
89 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 ...
4
votes
2answers
21k 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 ...
1
vote
2answers
1k 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
3k 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 ...
1
vote
1answer
359 views

PostgreSQL LDAP authentication

We are setting up a PostgreSQL 9.1 database at work. There are no classified data in the database, but we want to know who is doing what. We are using LDAP authentification on the network, so we would ...
0
votes
1answer
89 views

Recovering Postgres database

OK. I'm in a bit of trouble. I've got an Ubuntu, had Postgres 9.1 installed. There was something wrong with it - I couldn't start it for some reason. Out of panic, I backed up ...
1
vote
1answer
4k views

Error: Could not read Block X of relation base/Y/Z

We have some serious issues with our PostgreSQL-Server (Version 8.4). Our webapplication uses jdbc to connect to the PostgreSQL Server. Suddenly our webapplication cant connect to the PostgreSQL ...
2
votes
1answer
113 views

PostgreSQL: How can I setup Master-Slave replication with DML changes in Slave

I have a master database (PostgreSQL 9.0) and I need to setup a streaming replication on a slave. Slave db can read/write and make schema level changes too which shouldn't write back to master. Can ...
1
vote
1answer
461 views

pg_ctl start gives FATAL: role does not exist

On my system, whoami returns Dan. I run initdb with the --username=PgUser argument. Then as OS user Dan I run pg_ctl start on the newly-initialized cluster, and it gives this output: LOG: database ...
3
votes
1answer
991 views

Best practice for storing record metadata

What is the best practice for storing metadata of individual records in a database? I need to store common meta data such as creation time and time of last update for many tables in my database. I ...
1
vote
2answers
244 views

Prune unused joins

I have expected that selecting from a view would not join the tables from which I don't query any values, but it doesn't seem to be the case. I have a bunch of tables with the following structure: ...
6
votes
1answer
803 views

PostgreSQL “freeze”/“unfreeze” command equivalents

In Derby (an embedded database written in Java which is mostly used for testing or prototyping) there are "freeze" and "unfreeze" commands which can be used during an online backup. "Freeze" simply ...
0
votes
1answer
343 views

How to create fault tolerant databases? [closed]

I have a local application running on a LAN. Sometimes a database gets shutdown. In such a scenario how can I keep the application working? Can I have two concurrent databases so that one picks up the ...
0
votes
2answers
229 views

Mirrored tables on PostgreSQL

I have some values under "account" and I need to have the same values under "invoice", so, I'm trying to find a way to symlink my tables, so every time one gets updated, the another have the new value ...
0
votes
0answers
38 views

PostgresPlus advanced server replication testing issue

I made the replication on my Windows Server 2003 system as master DB and another XP system for slave DB. I just made the configuration, but I don't know how to check the replication of database. I ...
5
votes
2answers
989 views

PostgreSQL: Executing DDL on every schema

I've a multi-tenant DB setup, and need to add some columns. I'm using schemas (and search_path) to partition my users, so I'm looking for a ubiquitous way to apply a DDL-schema change to all my ...
0
votes
1answer
830 views

Full text search on multiple tables in PostgreSQL

I have several tables that have related data. I want to have a search input box on the dashboard of the system that will search for anything (client, invoice, delivery note, ...). Is it possible to ...
2
votes
2answers
70 views

Postgres Problems with Phusion Deployment (Rails)

FATAL: Ident authentication failed for user "test-postgres" So I have a postgres server hosting a side of my database access for my Rails application and I have a database for production set up and ...