All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

4
votes
2answers
779 views

Identifying postgres db health tool similiar to mysqlcheck

I'd like to run a check against databases in postgresql to verify that everything is ok after the server crash. On mysql it would be mysqlcheck. I would like to know the postgres equivalent.
4
votes
1answer
10k views

Pg install: “The database cluster initialisation failed”

(Note: This question has been compeltely rewritten from its original form, so it reflects the actual problem and will help others find a solution to the same issue). I still haven't been able to ...
4
votes
2answers
712 views

What happens to the index of a primary key after a DROP CONSTRAINT?

I am running PostgreSQL 9.1.4. I have a table with many existing rows, and a bunch of other tables with foreign keys pointing to it, for which I am trying to : 1 - Remove the pkey constraint on the ...
4
votes
2answers
1k views

Continuous archiving: is it possible to use multiple machines?

Is it possible to use the archive_command to send to multiple machines? Is it just as simple as separating with a && since it is essentially just a command line? command1 && command2 ...
4
votes
3answers
2k views

Install PostGIS v2 for PostgreSQL v9.2

I have installed the latest stable version of PostgreSQL v9.2 and I want to install the PostGIS v2 extension. I've been searching for it for 3 days now, however can't find it. I did however find ...
4
votes
1answer
2k views

Is it possible to configure PostgreSQL to automatically close idle connections?

Clients connect to our PostgreSQL 8.3 database but leave the connections opened. Is it possible to configure PostgreSQL to close those connections after a certain amount of inactivity?
4
votes
1answer
541 views

Can you make SSL and SSL-only (hostssl) connections to PGPool?

The new version of PGPool (3.2) has an "SSL" section in the conf file, and directions on how to use it in the manual. However, pool_hba.conf specifically says that PGPool doesn't support SSL (i.e. ...
4
votes
1answer
2k views

How to rename a Postgresql cluster?

How do you rename a Postgreql cluster? I googled and found this old web page, which said just to change the name of the directory. I tried it but it didn't work: $ pg_lsclusters Version Cluster ...
4
votes
1answer
604 views

Dropped database still shows but causes errors PostgreSQL

I had dropped a database a couple weeks ago but it still shows with \l. I've tried using DROP DATABASE mixmobi_master; and the first time it gave me a confirmation message but mixmobi_master was ...
4
votes
1answer
662 views

Single node parallelism with PL/Proxy?

I have seen some suggest that single node parallelism is possible, but all of the material I have found so far deals with parallelizing across nodes. I am interested in getting large static queries to ...
4
votes
1answer
6k views

How can I generate scripts for all functions in pgAdmin?

I am using pgAdmin version 1.10.5, and I've checked the properties and menu options but couldn't find anything on the subject. For example, I just want to generate a script that includes the ...
4
votes
1answer
807 views

What are all the reasons/possibilities that may fail to release disk space occupied by TEMPORARY table?

We're using PostgreSQL v8.2.3. Ours is a web-based application and we're using pgpool-II v 2.0.1 purely for connection pooling (we don't use other features of pgpool like Replication, Load Balancing, ...
4
votes
1answer
2k views

PostgreSQL: Corrupt primary key, inconsistent table

While recovering from a cloud failure, I found that some tables on a PostgreSQL database are behaving strangely. These tables are indexed using a primary key, but a pg_dump yielded duplicate fields, ...
4
votes
1answer
603 views

PostgreSQL relation size does not sum up

I have a very large table, with blob fields, called data. I've tried to figure out why it isn't cached well and repeated SELECTs are quite slow: => SELECT ...
4
votes
1answer
46 views

dumping database with “compressed data is corrupt” errors

I'm using pg_dump to dump a large (396 GB) PostgreSQL database: pg_dump --clean --create mydatabase After running for a day, a statement fails with ERROR: compressed data is corrupt and pg_dump ...
4
votes
1answer
31 views

Dynamic form builder database optimization Postgresql

Hi I have a project that create forms dynamically I use a model like the following: forms ----- id (PK) name (other fields) form_fields ------------- id (PK) form_id (FK to forms.id) ...
4
votes
1answer
41 views

Fastest way to find (rare) rows with a null value among millions of rows?

If I have an addresses table with ~ 4 million rows in a Postgres database. Is there a way to optimize the following query so that I can find the few rows where address_id is NULL? Sometimes I need to ...
4
votes
1answer
66 views

I Need Help Setting Up Roles and Passwords in PostgreSQL

I'm using the postgres.app on my Mac Mini Server running version 9.4.0 using El Capitan. I have not done any customization in pg-hba.conf. I do not have a .pgpass file. I believe that .pgpass is used ...
4
votes
1answer
198 views

Cannot revoke default privileges from postgresql user

I am using the psql tool: Command: my_db=# \ddp Result: Default access privileges Owner | Schema | Type | Access privileges ...
4
votes
1answer
102 views

pg_dump - schema not found

I am trying to dump a schema from Postgres 9.4, running on Cents 6.5, but I get an error: pg_dump: No matching schemas were found Here is the command I am running: pg_dump -U postgres -n ...
4
votes
1answer
59 views

Postgres query plan has high rowcount, slow execution

I've identified this snippet of a query as running at incredibly slow speeds, although it's quite simple. It aims to find all the records in Table1 which don't have a more recent entry in Table2 with ...
4
votes
2answers
327 views

How to log custom messages from inside a PostgreSQL transaction?

I have a software that generates transactions and executes them. I would like to asynchronously inspect about the transaction progress in term of the simple proportion of queries executed over total ...
4
votes
1answer
82 views

Why does it take so long for Postgres to return a sequence number?

I have an application that does bulk loads into a large table (100 million rows). I am using Postgres' COPY FROM functionality to load data from a flat file. The target table has a primary key of id. ...
4
votes
1answer
48 views

Persistent inserts in a UDF even if the function aborts

I have a rather complicated UDF (moves around a bunch of tables as well as creates a bunch of new ones) where multiple aborts may occur. Before every operation I would like to record the time when an ...
4
votes
2answers
304 views

How to reinstall PostgreSQL over an existing installation

I am trying to upgrade an installer created with NSIS that installs postgresql. It used to install postgresql 8.3 but we want to upgrade the DB to 9.4.4. The old version on of the DB used to be an ...
4
votes
3answers
99 views

PostgreSQL GRANT ALL on mutliple tables using LIKE

I have more than 100 tables named public.test_* How can I easily GRANT ALL access to the user test to all that tables at once? I tried with: GRANT ALL ON TABLE public.test_* TO test; But it is ...
4
votes
1answer
142 views

Combine multiple queries into 1 query

I have a message table in Postgres 9.4 that contains a words field of array type, with random words of a message. Currently I have millions of messages: \d messages Table ...
4
votes
1answer
153 views

How to correctly use PostgreSQL to limit multiple and/or concurrent executions of a task

Let's assume you have a task FOO that can be queued once every minute, and a pool of 50 workers that can be paused. The queue is paused for 10 minutes, and 10 FOO tasks are queued. When the queue is ...
4
votes
1answer
2k views

Restoring plain-text pg_dump with psql and --disable-triggers

I had to perform some tests with a short script to update some "legacy" data in one of my tables. Cautious as I am, using an untested script, I decided to backup the relevant table before doing so. ...
4
votes
1answer
449 views

Can't get Dell PE T420 (Perc H710) perform better than a MacMini with PostgreSQL

It's months that I'm trying to solve a performance issue with PostgreSQL. SYSTEM CONFIGURATION Our deployment machine is a Dell PowerEdge T420 with a Perc H710 RAID controller configured in this ...
4
votes
1answer
204 views

SQL - Inverse Selection

I have two tables in my schema students(id text, matric text) attendance(id serial, a_date date, matric text references students matric) To get records showing date and student present, I can run ...
4
votes
1answer
998 views

Pros and cons of using many schemas in PostgreSQL as opposed to just one?

For a large SAAS application (backed by PostgreSql 9.4), with over 300,000 accounts (and growing), what are the pros and cons of using a schema per account to partition the data vs putting all the ...
4
votes
1answer
298 views

How can I implement a sequence for each foreign key value?

In a PostgreSQL (9.3) database, I have a one-to-many relationship between Accounts and Customers, so within the Customers table, I have a globally-unique id for the primary key of each Customer, plus ...
4
votes
1answer
2k views

Is there a way to insert multiple rows into a table with default values for all columns?

I can insert multiple rows into a table with default values for all columns the RBAR way: create table course(course_id serial primary key); do $$ begin for i in 1..100000 loop insert into ...
4
votes
1answer
101 views

Counting repeated occurences for a group of fields

I am trying to count the continuous repeated value for a field in order to obtain the maximum repeated occurrences for each person in the table. eg: Time person Result 08:12:36 ...
4
votes
1answer
455 views

Optimal hash technique to index large text

Erwin Brandstetter thankfully saved me from myself when I tried to create unique indexes on textual columns with large lengths. The upper limit for the insertion rate is tens of billions of rows per ...
4
votes
2answers
203 views

Update SQL n:n table with multiple associations

I have done this before, but before I mindlessly repeat something that I consider as a hack, I'm asking here. I have 3 tables (details left out for clarity) : inv_items id bigserial (PK) sku ...
4
votes
1answer
5k views

MySQL failing and very slow on importing a large file

I have a 47 GB MySQL dump of a single table: http://dumps.wikimedia.org/commonswiki/latest/commonswiki-latest-image.sql.gz I ultimately want it into PostgreSQL, but since I didn't figure out an easy ...
4
votes
1answer
636 views

PostgreSQL does not use all available IOPS on a single operation

Using PostgreSQL 9.3 on Windows 7x64, CoreI7, SSD. Assume a PostgreSQL table with 8 Int32 columns and following C# code to fill the table. using (var connection = new NpgsqlConnection("User ...
4
votes
1answer
774 views

Determine the currently executing statement in a long running PL/pgSQL stored procedure

I have a long running PL/pgSQL stored procedure in PostgreSQL. How can I determine the currently executing statement in it?
4
votes
3answers
10k views

PostgreSQL: How to backup only One Schema from a database and restore it on another server

I have a database named "A" which has two schemas "B" and "C". I want to backup and restore Schema "B" on a different server? not sure how to do this as I am new to Postgres. Do I have to create a new ...
4
votes
2answers
3k views

Postgres log files not rotating

I have a postgres system with a hot standby server. They use the same config file. I've setup the log rotation to use log_rotation_size and log_rotation_age so that the master server is rotated every ...
4
votes
1answer
548 views

Choosing A PostgreSQL Authentication Method For A Large Course

I am teaching a first course in databases for the first time. Students will need to have a database management system to which they can connect to do much of their work for the course. I have chosen ...
4
votes
1answer
146 views

Fill factor based on index ranges

I'm designing a Postgres database for an events app. The app lists events sorted by when they start. Initially the app displays only 30 events. As users scroll through the list of events, more ...
4
votes
1answer
3k 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 ...
4
votes
1answer
3k views

How to fine-tune Postgres for IO usage?

I have a Postgres database I am hosting in a quad core server with 8 GB of RAM. At any time, there can be 100 or so reads and writes occurring concurrently. I am experiencing high IO wait time. When ...
4
votes
1answer
637 views

Streaming Replication Failover - how to point second slave at new master?

I have a Postgres database HA cluster I've set up in a lab. I'm using PgPool-II to manage fail-over. For the cluster itself, I have three identical Postgres 9.2 servers set up like so: Server A - ...
4
votes
1answer
201 views

After Rackspace server creation, PostgreSQL query planner doesn't work as expected

We created an image of one of our database servers in Rackspace. Then, we created a new server using that image, expecting things to work. However, the index performance we have seen seems to be ...
4
votes
2answers
4k views

issues with encoding and pg_dump/restore between windows and linux

I have a dmp file I have created using pg_dump encoded in WIN1252. What I would like to do is import it using pg_restore to a database on a Linux machine. The problem is that on the windows machine ...
4
votes
2answers
536 views

What are my options for GUI based Administration & Management of PostgreSQL

I have been given the task of working with PostgreSQL 9.1. I have previously worked with Microsoft SQL, and had used the SQL Management Studio. I had used Oracle's SQL developer as well as TOAD when I ...