Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [postgresql-9.2]

PostgreSQL version 9.2

247 questions
116
votes
7 answers

Very slow DELETE in PostgreSQL, workaround?

I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign keys referencing the main schema and not the other…
jd.
  • 1,262
  • 2
  • 9
  • 6
70
votes
3 answers

Why is a new user allowed to create a table?

I'm wondering why a newly created user is allowed to create a table after connecting to a database. I have one database, project2_core: postgres=# \l List of databases Name | Owner |…
andreas-h
  • 833
  • 1
  • 6
  • 6
51
votes
7 answers

Why does Postgres generate an already used PK value?

I'm using Django, and every once in a while I get this error: IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey" DETAIL: Key (id)=(1) already exists. My Postgres database does in fact have a myapp_mymodel object…
orokusaki
  • 1,209
  • 2
  • 12
  • 21
40
votes
4 answers

how to track progress of a large postgres dump

Is there a way to see the progress of a pg_dump operation on a big db (ie > 1GB)? adding the -v option just dumps text on the screen, but doesn't give me much meaningful tracking information.
abbood
  • 503
  • 1
  • 4
  • 7
37
votes
1 answer

VACUUM returning disk space to operating system

VACUUM usually does not return disk space to operating system, except in some special cases. From the docs: The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will…
Vadim Samokhin
  • 635
  • 1
  • 8
  • 13
22
votes
4 answers

Cannot shutdown old postmaster when upgrading to Postgres 9.2

I'm upgrading to Postgres 9.2.2 (from 9.1.4). When I try to upgrade the DBs using: pg_upgrade -b /usr/local/Cellar/postgresql/9.1.4/bin -B /usr/local/Cellar/postgresql/9.2.2/bin -d /usr/local/var/postgres91 -D /usr/local/var/postgres I get the…
Luciano
  • 1,771
  • 3
  • 12
  • 8
18
votes
2 answers

How to view the query of another session in pg_stat_activity without being super user?

I have a Postgresql 9.2 database. Two users are created on this database. When I perform the following query as superuser, I can see everything. select * from pg_stat_activity However, is it possible to acheive the same result without being…
Stephan
  • 1,513
  • 4
  • 18
  • 27
17
votes
1 answer

INSERT using results of CTE INSERT to provide unique id values

I am writing a job to transform data from an old design into a new design. In this process, I need to take the id from an insert into a separate table and use that in an insert to the target table, as such: CREATE TABLE t1 { t1_id BIGSERIAL, …
Joishi Bodio
  • 3,508
  • 2
  • 17
  • 20
15
votes
1 answer

Order by distance

If I have a query returning nearby cafes: SELECT * FROM cafes c WHERE ( ST_DWithin( ST_GeographyFromText( 'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')' ), ST_GeographyFromText('SRID=4326;POINT(-76.000000…
Gandalf StormCrow
  • 615
  • 1
  • 8
  • 17
15
votes
2 answers

Postgresql function to create table

I want to create a function in order to create a table with a specific structure pasing part of the name of the table as an argument so the name of the table is t_ . Similar to this: CREATE OR REPLACE FUNCTION create_table_type1(t_name VARCHAR(30))…
Alan Cor
  • 357
  • 2
  • 4
  • 8
14
votes
2 answers

PostgreSQL - Create view with autoincremental column

I have a PostgreSQL table, and I need to create a view with a new column. This column needs to be an auto-incremental column starting at 1 and going to N. Is this possible to do without effecting the original schema of the legacy data structure?
code base 5000
  • 285
  • 1
  • 4
  • 8
13
votes
2 answers

is it possible to run postgres with no WAL files being produced?

I am looking at a way of running a 9.2 postgres database on redhat 6 without any WAL files being produced. We have a end of day batch process which generates a large number of WAL files (and has filled the disk a couple of times). We also take a…
davegreen100
  • 572
  • 4
  • 12
  • 25
12
votes
1 answer

2 B-tree indices OR 1 GiST index on tsrange -- which will perform better?

I have a table which is storing reservation data using the columns starts_at & ends_at Whenever I'm querying the table to find overlapping reservations, I have an option of using one of the following queries: SELECT * FROM reservations WHERE…
Saurabh Nanda
  • 333
  • 1
  • 4
  • 16
12
votes
1 answer

Performance impact of setting PostgreSQL to log all statements

PostgreSQL 9.2.2 on Windows 7 64-bit. The setting log_statement in the postgresql.conf file specifies what statements to log (none, DDL, data-modifying, or all). To what extent does setting log_statement = 'all' degrade performance? I've read that…
bwDraco
  • 223
  • 2
  • 8
12
votes
1 answer

how to dump a case-sensitive table within a specific schema

I'm trying to dump a case-sensitive table named app_auth.User by executing the following command pg_dump --schema=app_auth -t '"User"' -U em3local -h 127.0.0.1 -Fc -a -f but what I'm returned is: pg_dump: No matching tables were…
Bertuz
  • 255
  • 2
  • 7
1
2 3
…
16 17