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)

14
votes
1answer
9k views

Superuser is not permitted to login

I created a superuser portal with the following command create role portal with superuser password 'portal' When I tried to login into postgres with portal user I am getting the following error in ...
1
vote
1answer
37 views

stored procedure vs. function in postgresql and which is better for executing dml statements

I'm using postgresql 9.3. If I want to add the following steps and call these steps as a routine, would it be better to use a function or stored procedure? My basic steps are: RENAME TABLE ...
2
votes
1answer
314 views

REST webservice as a Foreign Data Wrapper in postgresql

I know it's possible to embed data from a foreign resource in a postgresql database as a foreign table and have seen examples including redis, another postgres database, etc but I can't really find ...
0
votes
0answers
17 views

Postgres: Arrays instead of intermediate tables for many-to-many relations

I have a PostgreSQL 9.3.7 database scheme representing a directed graph of vertices and edges with some many-to-many relations. I'm only ever interested in getting all vertices that a given vertex ...
1
vote
1answer
27 views

Syntax error in postgresql query

I am trying to migrate a sql query into PostgreSQL. After running the query I get this error: ERROR: syntax error at or near "(" LINE 20: ..._date is null or fcm.effect_end_date>=current_date()) ...
3
votes
1answer
120 views

Estimating the size (of the records) and the overhead in Postgres

Consider the following table in Postgres 9.4: CREATE TABLE t ( a1 bigserial, a2 bigint NOT NULL, a3 bigint NOT NULL, a4 integer, a5 timestamp with time zone NOT NULL, a6 timestamp with ...
1
vote
1answer
47 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 ...
1
vote
1answer
16 views

Accurate PG database size for comparing representations

I have a Java/JDBC program that takes a sample file and imports the data into the database, shredding it across multiple relations. The program does this multiple times for several different ...
1
vote
1answer
33 views

Streaming replication and lag when performing VACUUM on master

My streaming replication standby server is showing a delay of a few minutes when vacuuming a large table on the master. What is the reason for the lag, and is there a way to reduce it? Both servers ...
1
vote
2answers
20 views

Migrating PostgreSQL files to new server

We have a Win 2008 server that went down because of cpu failure. We were running parts of the Atlassian stack (JIRA, Confluence, Stash, and Bamboo) on it using PostgreSQL as the database. I now have ...
4
votes
1answer
861 views

Could not open file “pg_clog/0000”: No such file or directory

My local PostgreSql 9.2 database won't start anymore this morning. I am on Windows 7. Yesterday, I performed a Windows Update before going to bed. I have checked the log and found: 2014-05-28 ...
4
votes
0answers
83 views

Why is query thousands of times slower with DESC?

I have a PostgreSQL database (AWS RDS) and ran the following queries in psql: livenet=> EXPLAIN ANALYZE SELECT * FROM outputs ORDER BY height LIMIT 10; Limit (cost=0.57..36.82 rows=10 ...
1
vote
1answer
34 views

Update staging data with production data in Postgresql

I'm using PostgreSQL (version 9.3) on my server and I want to update the data in my staging instance database with the data in my production instance database. Each database is controlled by a ...
-2
votes
0answers
10 views

I want to move the listbox one to another listbox and update the particular listbox value [migrated]

Here, Below i used the code. I am new in VBA. I am trying to move the one list box to another listbox, but I am not able to update the database table. I am struggle on few days. Why this error came ...
6
votes
0answers
243 views

Modification to GEQO (Genetic Query Optimization) of PostgreSQL

I need to implement a functionality that is in line with the GEQO functionality of PostgreSQL. I understand that GEQO approach is to encode query plans as integer strings and GEQO generates these ...
0
votes
0answers
29 views

Compare two databases

I want to compare two PostgreSQL database dumps. The output of pg_dump is not sorted. This makes tools like diff useless. Background: We update the way we set up a custom database. After our ...
8
votes
3answers
12k views

PostgreSQL 9.1 Hot Backup Error: the database system is starting up

I have been working on a hot backup for Postgres 9.1 for awhile and have run into a consistent issue. After restarting Postgres on the slave server the pgstartup log file and the daily log file under ...
3
votes
1answer
33 views

Local Backup from Amazon RDS PostgreSQL database

I have a PostgreSQL database on an Amazon RDS instance. I want to have a local dump of this instance. So far, I've tried with this: PGPASSWORD="Password" pg_dump -h ...
1
vote
1answer
40 views

Pattern matching in PostgreSQL? [closed]

I have a string in which need to extract the date, Example: S01_XXX001_000_min_01_20150526T123440583.arc , S01_XXX001_000_20150526T123440583_min_01.arc based on the pattern like 'Numeric T ...
1
vote
1answer
43 views

To find data in Multiple tables and delete it?

I am using a PostgreSQL server. Suppose I have 200 values in a master table that need to be deleted. There are 9 other tables referencing this master table. The other 9 tables not only reference the ...
2
votes
1answer
570 views

Trouble with installing additional modules on Postgresql 9.1 on ALT-Linux

I'm trying to create extension under postgresql 9.1 (Linux): CREATE EXTENSION pg_trgm; but got error: ERROR: could not open extension control file "/usr/share/pgsql/extension/pg_trgm.control": No ...
0
votes
2answers
128 views

Schema for lists of relationship differences

Say there is a data model that allows for relationships to be defined either explicitly or as a list of records added or removed relative to some existing item: Item <–> *Properties and Item ...
1
vote
1answer
36 views

Does VACUUM FULL need ANALYZE

I'd be grateful for some insight into the VACUUM functionality within PostgreSQL 9.3. I read through the documentation and searched around a bit but could not find a definitive answer to this. I'm ...
2
votes
1answer
214 views

Partial unique constraint spanning multiple tables in postgres

To enforce partial uniqueness in postgres, it is a well known workaround to create a partial unique index instead of an explicit constraint, like so: CREATE TABLE possession ( possession_id serial ...
1
vote
0answers
26 views

Copying Python data types to Postgres [migrated]

I feel like this is a very basic question, but I cannot find a comprehensive answer on this. I am wondering what is best practice for copying python data types, like lists or dictionaries, to a ...
1
vote
1answer
109 views

difference between pg_dump command's --column-insert and --attribute-insert

Background: I'm trying to create a dump file that only contains the data in my database. I would like it to create the data with the INSERT command. In reading the manual, ...
3
votes
1answer
167 views

Problems restoring backup from base backup + WAL with WAL-E

I'm trying to do nightly restores of the main database on a separate node. I want to do weekly full restores and nightly restores by consuming the latest WAL files. On the master I have wal_level = ...
2
votes
1answer
970 views

How to configure pgpool for reasonable speed

I have a problem with pgpool II configured in master-slave mode with replication on slony. I have configured postgres databases on each server to run as fast as they can and this was verified by me ...
0
votes
1answer
679 views

Configuring Postgres 9.2 streaming replication with Redhat Clustering Suite for High Availability

I'm in the process of configuring PostgreSQL 9.2 streaming replication with Redhat Clustering Suite for high availability. I have 1 master and 1 hot standby configured to replicate the data from ...
0
votes
1answer
47 views

Divide a PostgreSQL database into schemas

My Postgres database will be focused on multidisciplinary organizations. The project axis is: a person could belongs to diverses disciplines (ex: arts, sports) for the same organization, and this ...
2
votes
2answers
127 views

manual execution of specific remote query with postgresql_fdw

In 9.4b2, postgresql_fdw doesn't know how to "push down" aggregate queries on remote tables, e.g. > explain verbose select max(col1) from remote_tables.table1; ...
2
votes
1answer
45 views

postgresql - how and why indexes are bigger than their tables

I'm using postgresql 9.3 and trying to understand how and why indexes are bigger than their tables. Sample output: database_name | database_size | table_name ...
0
votes
0answers
10 views

How can I create a copy of postgis db from one windows machine to another

I have postgres 9.3 database with postgis extension. I want to create a new database on different computer and import all the tables from the source database. In mssql it would be simple , just ...
0
votes
1answer
227 views

pg_basebackup giving “could not receive data from client: Connection reset by peer”

I'm trying to get data to a new slave box from my new master (previously the slave). I've tried the steps already and it worked previously. Now when I run the following command: pg_basebackup -h ...
-1
votes
1answer
28 views

How to setup path to access psql from command line in Mac [closed]

psql does not work even path was set .bash_profile. Path was set export PATH=/Library/PostgreSQL/9.4/bin:$PATH in .bash_profile.
1
vote
2answers
136 views

Limiting read access in postgres to few rows

We have a requirement where we want to limit the maximum no. of rows that a select query can return when its done using a particular login. So basically we would like to append a limit clause to any ...
0
votes
1answer
26 views

Backup-strategy for redundant data in postgresql-db.

I have a (postgresql-9.3)-table that contains images as a bytea-column as well as several other columns that contain extracted information from these images. (Some feature points, descriptors). These ...
2
votes
3answers
48 views

What schema for different relation from two tables to another?

There are three tables instructors, meeting_points and bookings. Here are the relation I want: 1 instructor has 0, 1 or n meeting_points 1 booking has 1 meeting_point 1 meeting_point can be linked ...
5
votes
0answers
40 views

Bulk insert M:N relation in PostgreSQL

I need to import data from an old database to a new one, with slightly different structure. For example, in the old database, there is a table recording employees and their supervisors: CREATE TABLE ...
1
vote
1answer
47 views

Is there a way to add a column to a table without affecting performance?

How do you add a column to a table in production without affecting its performance? Or is this impossible? I'm asking about Postgres.
2
votes
2answers
44 views

Calculate number of consecutive years (not gapless)

I have a request that wants to look at numbers of consecutive year buyers. For example, I have a table like this: Order_year, Contact 2015, 123 2014, 123 2013, 123 2011, 123 2010, 123 2015, 234 2014, ...
2
votes
1answer
38 views

How do I prevent my query from needlessly generating rows it'll throw away later?

I have the following query: SELECT stream_points.time AS point_time, stream_points.value AS point_value, stream_predictions.value AS prediction_value, ...
0
votes
2answers
46 views

Approach to take regarding a field that is frequently re-computed

I have a few fields with text data in a main table which is processed in a certain way to produce a processed version of that piece of text. Now, the way in which this text is processed is expected ...
0
votes
1answer
119 views

11.2GB text file import MySQL or PostgreSQL

I am out of my depth a bit perhaps but submit this query hoping someone has some experience importing large (11.2GB) tab delimited text file (2.2GB tar.gz file) into either MySQL 5.5.41 or PostgreSQL ...
0
votes
1answer
61 views

Postgresql Hot Standby - How to determine it's fully mirrored?

I have set up a hot standby of a postgresql server. It all seems to be working, but I just want to be sure that I'm not missing something. In /var/lib/pgsql/9.2/data/pg_log/postgresql-Wed.log I have ...
0
votes
1answer
415 views

pgpool2 not working on my Ubuntu i686 athlon i386 GNU/Linux

I am trying to implement replication and load balancing on my Ubuntu i686 athlon i386 GNU/Linux.for creating database clustures I have installed postgresql contrib 9.1.11 having initially database ...
0
votes
0answers
25 views

Replicating table level PostgreSQL 64bit windows

I need to replicate some tables on PostgreSQL 64bit for Windows (master -> slaves replication model) and I can't find any tool that can achieve that. Slony-I work very well for PostgreSQL 32bit for ...
0
votes
0answers
23 views

What does `@@` mean in a PostgresSQL query? [migrated]

What does the "at symbol" mean in a PostgreSQL query? For instance, I'm looking at a query where a WHERE condition is: WHERE (to_tsvector('german', nodes.name) @@ plainto_tsquery('german', 'münd')) ...
1
vote
0answers
557 views

Postgres database zero down time production deployment

I need some idea about how I can deploy a financial application in production environment using PostgreSQL DB with zero down time. I have two servers in Master-Slave design. Currently I am giving ...
1
vote
1answer
1k views

PostgreSQL : drop column from view

I have a VIEW where I'm attempting to create an evolution script for, so I can add a column to it. That part works fine; column added just fine. However, the reverse does not work; remove that last ...