Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
0
votes
1answer
155 views

Dependent Transaction in separate Database-Connections

I am quite new to database and I am in the following situation. I have two tables defining a 1:n relation, let's say Company and Employee which I access in two separated database connections in ...
0
votes
1answer
762 views

Cannot import database exported by phpPgAdmin

I have exported database with phpPgAdmin tool - format sql, structure and data. When I try to import this sql using: psql command \i filename.sql After some time (file is 2GB) I get following ...
0
votes
1answer
43 views

PostgreSQL: create user who can create new roles?

I would like to create a user who can create other roles. For example: user "dennis" can create roles. User "dennis" owns database "main" and can only access this database. Would it be possible to ...
0
votes
1answer
37 views

Postgres 9.5 import to MSSQL Server 2012 r2 verification issue

I have a postgresql table with 8 million rows. I need to copy it over to MSSQL. I do not have access to the console so I am unable to use PG_Dump. I have the ODBC driver, and have successfully linked ...
0
votes
0answers
2k views

psql multiline commands: How to edit previous lines?

When writing a query across several lines in psql, sometimes I realize I've made a mistake in one of the lines previously written. In the following example, select * form ( etc, etc... there is a ...
0
votes
2answers
204 views

PostgreSQL 8.1 Corruption Issue

I have an old Postgres server that some major corruption problems on one of the databases. We first noticed the issue when the drives started to fill up on a database that should have plenty of space. ...
0
votes
1answer
144 views

Subquery or Inner Join?

I have this simple query that finds all transactions from accounts belonging to a specific login, with some extra conditions on the transactions. SELECT t.id, t.date, t.amount, t.description FROM ...
0
votes
1answer
113 views

how to update new parent node in the middle of the postgresql ltree by some simple update SQL statement? [duplicate]

I follow this URL to make some SOP for my colleague. How to update the parent/child of all rows in the tree (ltree)? However, my colleague hope to use some simple statement about "UPDATE" for adding ...
0
votes
2answers
45 views

Are there any Database functions which allow access to other network services in PostgreSQL

I have set up a new postgres installation and have to meet the security requirements from my company. There is one requirement were i'm not quite sure: Database functions which allow access to ...
0
votes
1answer
86 views

Postgresql: replica and HA

I've postgresql 9.4. I'm aware that there are many answer on DBA and SO, I'm aware of the wiki and the documentation. But anyway, I can't understand in a simple way what's the right way to have a ...
0
votes
0answers
674 views

pg_controldata 'Time of latest checkpoint' on standby doesn't change

About 2 weeks ago, pg 9.3 master space filled up, didn't die, continued operation after space was freed up, and it's standby has kept up with it ever since. However the standby pg_xlog keeps filling ...
0
votes
1answer
189 views

postgresql reindex frequency and monitoring

What's up guys. I have a couple of tables where i inserting much of data (and almost never deleting). Periodically i see that insert gains ExclusiveLock on index of table. So i guess that sometimes ...
0
votes
1answer
187 views

CTE Query copy value if exists into an existing table [closed]

I got the following query: WITH table_data AS ( SELECT UNNEST(array['Small Bus', 'Window', 'Test 1']) AS description, UNNEST(array['1dfsdf4', 'QSF-312', 'EXD-TUH']) ...
0
votes
0answers
79 views

Cannot delete Postgresql constraints that were former unique keys

I have modified unique constraints. I am adding a field to every unique constraint. To do that, I need to drop the existing unique constraints and create the new ones. I am trying to delete foreign ...
0
votes
1answer
370 views

postgresql statement taking ages to cancel in pgAdmin III

I was running a query in pgAdmin III and it was taking a long time. After 9 minutes I decided to cancel it using the stop button. The UI stated that it was cancelling and stayed in that state for ...
0
votes
1answer
96 views

Repopulating PostgreSQL Database with Data Directory from Another DataBase Instance

I have had to delete PostgreSQL for an issue and reinstall it, however before uninstalling I did not execute the command pg_dump. I still have the data directory from this instance. After reinstalling ...
0
votes
1answer
363 views

How are Postgres partitions counted for the table size limit?

Postgres has a limit of 32T in a single table, but what if the table is partitioned - is that 32T per partition now?
0
votes
2answers
307 views

pgbouncer on separate server than postgresql

Right now I am hosting postgresql and pgbouncer on the same Linode with 48GB of RAM and 12 Cores. The server gets many short lived connections per second. The server load will increase with time and ...
0
votes
1answer
595 views

Amount of time FULL VACUUM/VACUUM

I have a table "lng_grid_test", which, at the beginning, had around 600 000 000 around. The table is defined as : -- Table: public.lng_grid_test -- DROP TABLE public.lng_grid_test; CREATE TABLE ...
0
votes
1answer
22 views

Migrate schema updates onto existing schema

So we have three development environments from a programming point of view. Each environment has its own postgres database. Let's say a table gets an extra column in our development environment, we ...
0
votes
1answer
46 views

potgresql foreign key index unused for some columns on one specific server

I have a table publication, with two columns type_id and sub_type_id (there are, of course, more columns, but they are not important) on a server, all containing int8 foreign keys to tables type and ...
0
votes
1answer
171 views

Restoring postgres backup with WAL-E fills up disk

I am running Postgres and WAL-E in a Docker container built from a custom image based on postgres:9.4. WAL-E works great and I have restored from backups a number of times. However, the backups take a ...
0
votes
1answer
105 views

How to optimize query with order by

Have query which generate OpenERP ORM. Table have 100k rows. SELECT "tbl".id FROM "tbl" WHERE (("tbl"."active" = 'True') AND ("tbl"."is_company" IS NULL or "tbl"."is_company" = false )) ORDER BY ...
0
votes
1answer
39 views

Get A Count From Table

I want to see all fsid limit 25 from my table -- I tried this query Select * from btmt WHERE Count(fsid) > 1 limit 24 But this throws an error of Aggregates are not allowed in WHERE clause. ...
0
votes
0answers
418 views

Is there any way to reduce pg_largeobject size?

The Backup team are concerned about DB backup since it's huge and taking a long time to back up (physical backup - 300 GB - approx 5hrs). 90% of data is in pg_largeobject. I could remove loid which ...
0
votes
1answer
39 views

postgresql fulltext slow

I am trying PostgreSQL for the first time, but am finding the fulltext search very slow. My database has a table with 5 columns and 5 million rows CREATE TABLE table_test ( id integer NOT NULL, ...
0
votes
0answers
2k views

Cannot drop constraint due to FK references

I have detected a duplicate index on an id field on one of our tables, as that column is the PK, but there's also a separate UNIQUE index on such column, which is useless. But I cannot delete the ...
0
votes
1answer
53 views

What is the impact of DROP SCHEMA on concurrent read only workloads in PostgreSQL?

I have an ELT process that deals with a pretty small amount of data. I would like to have 1 schema called prod with the results of the previous ELT run and 1 schema called dev where I will perform the ...
0
votes
1answer
1k views

pg_stat_activity shows lots of easy queries running for minutes

After application server start its connection pool fills quickly and application can't work properly. I went through pg_stat_activity and noticed lots of queries running for minutes: But postgres's ...
0
votes
1answer
1k views

Dumping and restoring tables in PostgreSQL - ERROR: extra data after last expected column

I am trying to dump and restore multiple tables from a database on one server, to a different database on another server. Both are running Postgres 9.4 on linux. The command for the backup was: $ ...
0
votes
1answer
23 views

select duplicated rows by modified_date - PGSQL 9.2

Query: SELECT j.id INTO junk.ja_jobs FROM ( SELECT time_job, recurrenceid, clientid, creatortype, deleted, MIN(id) AS id FROM ja_jobs WHERE clientid = 33731 AND creatortype = 'legacyrec' AND ...
0
votes
1answer
476 views

Trigger function - PostgreSQL 9.2

The idea: 1 - The customer can add any value into users.code column 2 - If the customer does not insert data into it, the trigger/funciton has to do the job I am able to do that, using this SQL: ...
0
votes
1answer
489 views

Schema size [PG TOAST] - PostgreSQL 9.2

I've got the following query to measure the schema's size on my DB: WITH schema_size AS ( SELECT tab.table_catalog AS database_name, tab.table_schema AS schema_name, tab.table_name, ...
0
votes
1answer
86 views

Query Last Value in Fixed Length Array With Empty Values

I have a table that generally looks like: Name varchar | Location varchar[] --------------------------------------------- Lemoney | {Home,Work,Home,Stack Exchange,,,,,,} Other Dude | {Home,,,...
0
votes
1answer
48 views

Incomplete installation with unsupported locale

I was installing postgresql on Ubuntu 12.04 (Digital Ocean droplet). The locale variables in my environment gets transported to the DO box. When postgres is installed via apt-get install postgresql, ...
0
votes
1answer
645 views

Pooler (pgbouncer) + MasterDB + Slaves

During a spike, my connections go from 30 to over 140, as you can see below: I'm willing to set-up a pooler like pgbouncer, but have some questions as my scenario is not that easy: 1 Load Balancer ...
0
votes
1answer
111 views

Select one column by two different names in the same query

I have a table named Main that contains two fields id,name. I want to select the name column by two different names. For example: Table Main id:int, name:varchar(20) I have inserted the following ...
0
votes
1answer
59 views

BIG Binary column - PostgreSQL 9.2

I've got the following table: CREATE TABLE gorfs.inode_segments ( st_ino "gorfs"."ino_t" NOT NULL, -- Inode number the segment belongs to. alongside segment_index, it forms the table's primary key ...
0
votes
0answers
665 views

When to reindex postgres index?

I want to know if I need to reindex a PostgreSql index, ith SQL Server, I check for index fragmentation and then decides whether a Rebuild, a Reorganize or no action is needed. When should I reindex ...
0
votes
0answers
57 views

Why does Postgresql not write the username to the log file

I am trying to understand why, my database is logging without username in the queries. I discovered today that I can't see by looking at the logs who did a query. I think it is a configuration ...
0
votes
2answers
583 views

One connection vs multi connection

I'm redesigning my client's website in order to add about 16 mini sites for their web site. It's a website using PostgreSQL. And my question is: Performance wise, what is the better database design: ...
0
votes
1answer
77 views

Poor Query performance in postgresql 9.3

I have a simple query which is taking about 2 minutes to complete. select distinct a.statename, a.districtname, count(b.rel_globalid) as samplescollected from fulladmin a ...
0
votes
0answers
612 views

Query hung or way too long?

As part of the proccess of new items added to our system, some tables and relations are created programmaticaly via trigger, but it seems to be failing quite often right now. The DDL seems to be ...
0
votes
0answers
79 views

How to make sure vacuum is happening in postgres sql db

I have created a table with two columns and created index for the first column. Then inserted 2 million data using generate series function for first column (2nd column is having null). And executed ...
0
votes
1answer
40 views

Constraint Information - PostgreSQL 9.2

I was trying to delete some data from the table dm.billables: 1 - Select the id - PASS SELECT * INTO junk.clientid_10306 FROM dm.billables WHERE account_id = 10306; 2 - Delete the data from the ...
0
votes
1answer
2k views

Subquery uses ungrouped column “s.student_id” from outer query

I have the following tables: 1 teacher can have 1 or more students 1 student can have 1 or more homeworks I would like to know in a list of teachers, for each teacher if it has any student with any ...
0
votes
1answer
45 views

Deleting aged rows from postgresql table which don't have related entries in another table

I maintain a website where users post interesting links, and other users leave replies under the said links. I want to delete aged link entries under which no one replied. The postgresql database is ...
0
votes
1answer
112 views

SQL to DjangoORM query

I've model, class ModelA(models.Model): device_id = models.IntegerField() deal_sent = ArrayField(models.IntegerField) For given device_id, we can have a list of repeating deal_sent. For eg:- ...
0
votes
0answers
1k views

PostgreSQL crash when connection failed

Error log is: 016-02-21 21:22:34 GET DETAIL: Connection matched pg_hba.conf line 83: "host all all ::1/128 md5" .... .... 2016-02-21 21:22:37 GET DETAIL: ...
0
votes
1answer
827 views

PostgreSQL Client Certificate TLS error

PostgreSQL 9.4.6 OpenSSL 1.0.2f If my server or client certificate use SHA256, I get the following error on the client: psql: SSL error: tlsv1 alert decrypt error And the following log message on ...