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)

-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
votes
0answers
582 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, ...
-1
votes
2answers
897 views

Postgresql could not find pg_xlog [closed]

I'm trying to mount the WAL to a different disk. I stopped the server, and did the following: (I'm using Fabric) # Move pg_xlog to wals/path.dev/ dest_dir = "/home/ec2-user/%s/wals/%s" % ...
5
votes
1answer
374 views

What fillfactor for caching table?

I have heavily updated / accessed table where I store serialized java objects. They are in the table for 2-3 hours (also are being updated during that period) and then removed. Size of table is around ...
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
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?
2
votes
3answers
5k views

Starting postgresql service and specifying data directory

I have recently installed pg 8.4 on Ubuntu 10.0.4 using apt-get install. It installed pg as a service which starts ehenever the machine starts. However I have created a db cluster in a specific ...
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 ...
0
votes
1answer
501 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 ...
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
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
669 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 ...
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
1answer
55 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
0answers
193 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 ...
2
votes
2answers
9k 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
1answer
650 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
290 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
117 views

Tools and methodologies to keep to DBs aligned [closed]

2 DBs having schemas that represent the same semantic objects. The first one is production DB (Non-RDBMS, in-house implemented in-memory DB with shitload of RAM). Other is Postgres. Once in a while ...
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
2answers
236 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 ...
4
votes
1answer
3k views

postgresql: how do I dump and restore roles for a cluster?

Where are roles stored in a cluster, and how do I dump them? I did a pg_dump of a db and then loaded it into a different cluster, but I get a lot of these errors: psql:mydump.sql:3621: ERROR: role ...
1
vote
1answer
362 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: ...
1
vote
1answer
278 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
1answer
283 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
578 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 ...
0
votes
1answer
473 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 ...
2
votes
1answer
272 views

Problem with shortest distance calculation in PostGIS

While working with PostGIS pgrouting, for calculating the distance between two roads (lines) I'm using the shortest_path function. The logic is based on start_point(Start_id) and end_point(end_id) ...
0
votes
1answer
357 views

createdb command error : could not connect to database [closed]

I am using the following command for creating database in postgresql. createdb -D pg_default -E UTF8 -h localhost -p 5432 -U pramil -W pramil mydb but this command results prompt for password.When ...
5
votes
1answer
253 views

force PostgreSQL into recovery mode

I have a somewhat strange question. Is there a way to manually force PostgreSQL into recovery mode? I've not been able to find aything in the documentation. I've been working with a script ...
5
votes
1answer
145 views

How to schedule PostgreSQL replication?

I was reading postgresql replications solution but, even I just starting understaning how it works, another doubt has arrisen. I'll be using postgres internal replication solution but as far as I ...
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: ...
1
vote
1answer
61 views

Can pg_hba.conf trust processes?

Is it possible to configure PostgreSQL such that it can trust a single process, instead of a specific user?
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?
0
votes
1answer
295 views

A stored procedure to bulk load in Postgres or Oracle given a URL [closed]

Is there a stored procedure available in Postgres or Oracle that does a bulk load given a URL, as in: url_bulkload csvFile.ctl Where csvFile.ctl is # # csvFile.ctl -- Control file to load CSV ...
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 ...
0
votes
1answer
1k views

PostgreSQL server log connection message [closed]

I'm seeing the following message in PostgreSQL server log: LOG: could not receive data from client: No connection could be made because the target machine actively refused it. There are ...
1
vote
1answer
97 views

Partition of tablespace [closed]

My postgres server is running in a windows server and three tablespace are created. Postgresql server is installed in C: drive and the tablespaces are in D: and E: drive. So how to take a backup and ...
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?
1
vote
1answer
231 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 ...
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.
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
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 ...
1
vote
2answers
437 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
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 ...
1
vote
0answers
484 views

Remote connection to PostgresSQL in Windows 2008 Server is prompting the error below [closed]

The error: Unable to read data from the transport connection. an existing connection was forcibly closed by the remote host. The conditions: I have the following connection string: server = ...
6
votes
3answers
247 views

Is using SUM() twice suboptimal?

I know I have to write SUM twice, if I wish to use it in a HAVING clause (or use a derived table otherwise): SELECT id, sum(hours) AS totalhours FROM mytable GROUP BY id HAVING sum(hours) ...
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 ...
1
vote
1answer
2k 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 ...