Questions tagged [postgresql-9.4]

PostgreSQL version 9.4

111 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5
votes
1answer
2k views

Why am I getting permission denied on an ownership change?

My current user is pronto mediapop_staging=> select current_user; current_user -------------- pronto (1 row) This user a standard super user created on AWS RDS. mediapop_staging=> \du ...
5
votes
0answers
532 views

How do transactions affect PostgreSQL's REFRESH MATERIALIZED VIEW CONCURRENTLY command?

PostgreSQL 9.4 supports REFRESH MATERIALIZED VIEW CONCURRENTLY. I'm not sure, but I think the REFRESH command is viewed as DDL by PostgreSQL, and PostgreSQL wraps all DDL within transactions so that ...
4
votes
0answers
467 views

PostgreSQL full text search: How to change what classifies as a “word separator”?

Using ts_debug I can see what symbols Postgres treats as (what I call) a "word separator". Example: SELECT ts_debug('english', 'Hello. ABC') Result: (asciiword,"Word, all ASCII",Hello,{...
4
votes
0answers
1k views

Insertion into postgres database stops (hangs) after table reaches a certain size without error

We are currently trying to insert a large amount of data, about 27 million records with 200 columns, into a single table in a postgres 9.4 database. (Yes it is probably better to refactor the table ...
4
votes
2answers
3k views

How to deal with large offsets in select?

Table jtest with 200k rows, each row contains jsonb { id: "<uuid>", key: <index> } (<index> is integer 1-200k incremented per each row). There is also btree index on data->'key'. ...
4
votes
0answers
1k views

Creating a unique index on a materialized view with no unique columns?

I am using Postgres 9.4. I have created a materialized view from one of my tables and I want to be able to refresh it concurrently. I know from the documentation that in order to do a concurrent ...
3
votes
0answers
331 views

How do I fix multixact wraparound after it has already occurred?

I have a database which has a single table for which MultiXact wraparound has already occurred. Neither manual vacuums nor automatic vacuums are able to fix this condition. Manual vacuums fail almost ...
3
votes
0answers
52 views

Combine data sets

There are two table below, project_check_history is a history of some checked did on a project and project_check_metrics is that table holding the metrics collected about that project. Tables and ...
3
votes
0answers
375 views

INSERT INTO with RETURNING clause: return undefined columns?

Breaking it down to it's most simplest form... Is it possible to use INSERT INTO with a RETURNING clause to capture a non-existing column alongside the output? test=# \d x Table "public.x" ...
3
votes
0answers
1k views

“could not write to log file, Bad file descriptor” error when working with huge table

I'm having a big problem with a PostreSQL database. Whenever I do something with a huge table (over 9 million rows) the whole postgres process crashes with the following entries in the log file: ...
3
votes
0answers
437 views

Postgresql upgrade issue

I'm trying to test the Postgresql 9.3 -> 9.4 upgrade process on the development environment on windows and I get weird issue during the pg_upgrade invocation: PS D:\bin\postgres-9.4> .\bin\...
3
votes
0answers
2k views

Most efficient way to bulk insert or upsert data into multiple related tables

Given a PostgreSQL 9.4 schema with 3 related tables: CREATE TABLE a ( id BIGSERIAL PRIMARY KEY, a_name VARCHAR(80), a_field INTEGER ); CREATE TABLE b ( id BIGSERIAL PRIMARY KEY, b_name ...
3
votes
1answer
3k views

Use pg_dump with postgis extensions?

I'm using Postgres 9.4. I'm trying to dump a database (both the schema and the actual data) with the PostGIS extensions (set up using CREATE EXTENSION): $ pg_dump prescribing -U prescribing -h ...
2
votes
1answer
620 views

PG_DUMP on Replica DB server Error

I have a master - slave configuration with 9.4 version, but there is no WAL streaming replication configured. Customer simply copy xlog files on network share and replica applies it. I need to run ...
2
votes
0answers
148 views

Postgres 9.4 - pg_stat_database and others showing no stats

A database server running Postgres 9.4 shows no statistics when running select * from pg_stat_database - well only the numbackends is non-zero. The stats_reset is null. I haven't been able to find ...
2
votes
0answers
153 views

PostgreSQL database re-design considerations

We have a 2TB PostgreSQL database, which contains two groups of tables that account for about 90% of the storage used. These two groups can be loosely described as 2 big tables partitioned, the first ...
2
votes
0answers
888 views

How to transfer table from one database to another in postgresql

pg_dump -h host_ip -U postgres -d source_db -t aa_nqrgs.act_26022017 | psql -h destination_host_ip -U postgres -d destination_db I have tried this command to transfer a table from one server to ...
2
votes
1answer
2k views

Automatic recovery of the failed postgresql master node is not working with pgpool II

I am new to Postgresql and Pgpool II setup. I have configured the Postgresql HA/Load balancing using Pgpool II and Repmgr. I have followed the link to do the setup. The setup consist of 3 nodes and ...
2
votes
0answers
31 views

How to create ROW VALUE index

I have a table, lets call it mytable: CREATE TABLE mytable { id INTEGER, ts TIMESTAMP WITHOUT TIME ZONE, .. ) and I have following query in multiple client applications: SELECT ... FROM ...
2
votes
1answer
156 views

Postgres - Querying Min, Max and getting associated columns data specific to those result of those rows

I have query in which returns averaged values by site and month. What I want to know is the min and max of those monthly averages by site and (the difficult part) the month when each occurred. Here ...
2
votes
0answers
706 views

Postgres update query complaining about text value when only NULL values are passed

I am using Postgres 9.4.6. Is there any reason why the below update query would execute successfully here:- UPDATE "Predictions" SET "userid" = c."userid", "Fixture_No" = c."Fixture_No",...
2
votes
0answers
497 views

Some delays in heavy inserts

I have a lightweight web-application with current load of about 500 reqests/sec. Almost each request generates an INSERT request to the database. Some time ago I found that my application has short ...
2
votes
0answers
1k views

Update column type when materialized views depend on the column?

I'm working in Postgres 9.4. I want to alter the type of a column in a table: ALTER TABLE mytable ALTER COLUMN quantity TYPE integer; But I get this: ERROR: cannot alter type of a column used by a ...
2
votes
0answers
232 views

Old records disappearing after upgrade from 9.4.1 to 9.4.4

I recently upgraded from 9.4.1 to 9.4.4. Since then millions of rows have disappeared from several tables containing historical data. The severity of data loss seems to be directly proportional to ...
2
votes
0answers
20 views

Persisting typed variables

Is it possible to associate typed variables with an object, such as a database, table, or row? Suppose I want to store the schema version as a NUMERIC.
2
votes
0answers
467 views

pgAdmin export line overflow on large field

Background: I have a database of significant geographical locations downloaded from GeoNames open database. The create script for the table is as follows CREATE TABLE populargeonameswiki ( geonameid ...
2
votes
1answer
450 views

Postgres_fdw LEFT JOIN with foreign table slows query down massively

I have this base query and execution time around 400ms: SELECT s.parcelno FROM export.scans s INNER JOIN plnum_customerno cp ON cp.pl_number = s.parcelno WHERE s.type_of_scan = '05' AND ...
1
vote
0answers
20 views

Postgres Major version upgrade 9.4 -> 9.6. PITR compatibility across versions?

Just dug into postgres documentation and couldn't get a clear answer on this. With Debian Jessie hitting EOL for Debian LTS in June, we are upgrading our database servers along with our other servers ...
1
vote
1answer
102 views

Does the Postgres 9.4.9 update fix MultiXactId “does no longer exist” in addition to “has not been created yet” errors?

Postgres 9.4.9 has a fix in it's release notes which reads (emphasis mine): Prevent possible failure when vacuuming multixact IDs in an installation that has been pg_upgrade'd from pre-9.3 (Andrew ...
1
vote
0answers
78 views

Changing Postmaster File Name in PostgreSQL

I have two instances of PostgreSQL with master-slave replication between them and want to use GlusterFS. Everything works fine until the second instance wants to store the data in the GlusterFS. ...
1
vote
0answers
903 views

postgres :: FATAL: could not access file “pglogical”: No such file or directory

We installed pglogical from source code, we are running PostgreSQL 9.4 make USE_PGXS=1 clean all make USE_PGXS=1 install Installation was successful after updating the parameters, we tried ...
1
vote
1answer
293 views

ERROR: relation “codepool_codepoolseq_seq” does not exist SQL state: 42P01

Below is my script but I keep getting an error. -- Table: public.codepool -- DROP TABLE public.codepool; CREATE TABLE public.codepool ( codepoolseq bigint NOT NULL DEFAULT nextval('...
1
vote
0answers
16 views

Calculate percentages by using JOINS result in postgresql

I am trying to calculate percentages by joining 3 tables data to get the percentages of positive_count, negative_count, neutral_count of each user's tweets. I have succeeded in getting positive, ...
1
vote
1answer
72 views

How to get aggregate data from a dynamic number of related rows in adjacent table

EDIT: Unknowing of the rule that prohibits cross-posting, I also asked this on Stackoverflow and chose an answer over there. Since there's another (fully working) answer in this thread though, I won't ...
1
vote
0answers
328 views

Postgresql BDR - What causes replication_slots to go inactive?

I'm trying to hunt down what causes replication slots to go inactive. Might seem like a remedial question but I'm noticing that on my master database server, I have many inactive replication slots ...
1
vote
0answers
59 views

Grouping records time interval (start-end time) into groups of specific time interval (eg. 30 min)

I would like a query that would return the count of records that have their start-end time in specific time interval (eg. 30 mins). For example records: id|start|end 1|10:00|12:00 2|11:00|12:20 3|10:...
1
vote
0answers
59 views

Combining two event tables into one postgres view

I have two tables, events and event_logs. The events table stores the theoretical data on a recurring event whereas the event_log stores specific information on a particular datetime. Event +-----...
1
vote
0answers
468 views

PostgreSQL service stops immediately after installing Windows Fall Creators Update 1709

The server itself runs fine with pg_ctl -D <datadir> start But the service stops immediately ("...Some services stop automatically if they are not in use by other services or programs") ...
1
vote
0answers
457 views

Postgresql criteria for record to text conversion

Working on two servers, I found differences in the way Postgresql compose a record and converts it to text. I can't find which is the configuration involved in this difference. As a test, I run the ...
1
vote
0answers
1k views

Migration from postgres 9.4 to postgres 9.6

I did the migration from PostgreSQL 9.4 to version 9.6, but now the stream replication is not working, it stopped replicating. Script followed: installation of postgres 9.6 on the three servers (...
1
vote
0answers
459 views

“must be superuser or have the same role to terminate other server processes” with PostgreSQL

Postgres 9.4.12 locally and 9.4.7 on AWS RDS. There are two database users: app and untrusted_dev (the latter to mirror AWS RDS: NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION). app is ...
1
vote
0answers
522 views

PostgreSQL: Removing standby from streaming replication

I have PostgreSQL 9.4 streaming replication setup between three nodes (master and two hot standbys). I use repmgr to control the replication cluster. I need to retire one of the standbys and remove it ...
1
vote
0answers
125 views

Postgres 9.4 importing csv file causes connection abort

I have very simple database which i would like to import data from a csv file, so far i tested with a file with a few rows, and it worked. So tried to upload the real data to the database, which ...
1
vote
0answers
626 views

Faster way to do an Update which includes a window function

I am looking for a faster way to do a update query which includes a window function, and don't have any solid idea what might help. I am "denormalizing" a bit of data, which means moving the ...
1
vote
0answers
1k views

PostgreSQL v9.4, ERROR: must be owner of database test_db

I'm a new PostgreSQL admin. We have PostgreSQL v9.4.1 installed on a Linux server (Scientific Linux release 6.6). I'm getting the following error in my diagnostic log: 2016-07-10 02:54:12 EDT [...
1
vote
0answers
164 views

Add Shapefile loader plugin to PgAdminPortable

I'm using PgAdminPortable and I'd like to enable « PostGIS Shapefile and DBF loader » plugin. The issue is that the files are not structured as in a PostgreSQL complete install (see below). I have ...
1
vote
0answers
58 views

Function for matching email address returns full table of emails

I am using Postgres 9.4.6. I have created a Function for finding email address that match an email variable that is passed. However, when I search for 'user@email.com' I get a full table of email ...
1
vote
0answers
225 views

Replicate Postgres 9.2 to 9.4 with no downtime

My current set up has a database running on Postgres 9.2.4 and would like to move from AWS EC2 to RDS. However, AWS requires Postgres be running at least Postgres 9.4 in order to perform this ...
1
vote
0answers
52 views

Could auto_analyze cause inconsistent (and bad) query plans?

I have a simple table which stores metadata about users in an hstore column. This table has ~1.1M rows. Table "public.stitcher_participant" Column | Type ...
1
vote
0answers
133 views

pypl store function to update a record is not working

I am using PostgreSQL 9.4.7 and Python 2.7.6. I am writing a plpython function to update a row in user table and my code is as below: CREATE FUNCTION update_user(myid int, mymail text, myname text) ...