PostgreSQL version 9.1
Questions tagged [postgresql-9.1]
284 questions
384
votes
4 answers
How do I list all schemas in PostgreSQL?
When using PostgreSQL v9.1, how do I list all of the schemas using SQL?
I was expecting something along the lines of:
SELECT something FROM pg_blah;
Stéphane
- 4,945
- 4
- 17
- 12
201
votes
5 answers
Granting access to all tables for a user
I'm new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, and DELETE privileges on a low privileged user and enable those grants to apply to all tables in a specified database. I must be…
PlaidFan
- 2,115
- 2
- 13
- 7
143
votes
4 answers
Optimizing queries on a range of timestamps (two columns)
I use PostgreSQL 9.1 on Ubuntu 12.04.
I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my actual table that are not involved with this…
Stephane Rolland
- 7,903
- 8
- 29
- 40
89
votes
3 answers
How do I insert a row which contains a foreign key?
Using PostgreSQL v9.1. I have the following tables:
CREATE TABLE foo
(
id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
type VARCHAR(60) NOT NULL UNIQUE
);
CREATE TABLE bar
(
id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
description…
Stéphane
- 4,945
- 4
- 17
- 12
78
votes
4 answers
PostgreSQL: Remove password requirement for user postgres
I understand that, upon installation, PostgreSQL has no password for its db root user (postgres):
postgres=# select usename, passwd is null from pg_shadow;
usename | ?column?
----------+----------
postgres | t
(1 row)
... and one is advised to…
Marcus Junius Brutus
- 3,119
- 7
- 28
- 41
67
votes
6 answers
How to list all views in SQL in PostgreSQL?
How do I list all views for a database using an SQL command in PostgreSQL?
I would like something similar to output of the psql \dv command, but preferably just a list of view names. e.g.,
SELECT ...;
my_view_1
my_view_2
my_view_3
I'm running…
Rob Bednark
- 1,953
- 4
- 20
- 20
59
votes
2 answers
How to view the current settings of Autovacuum in Postgres?
I've found a million examples of how to set Autovacuum's configuration settings, but can't seem to find how to list the current configuration.
Postgres 9.1 is the version I'm most interested in.
Peter Groves
- 1,035
- 2
- 9
- 7
49
votes
2 answers
Configuring PostgreSQL for read performance
Our system writes a lots of data (kind of Big Data system). Write performance is good enough for our needs but read performance is really too slow.
The primary key (constraint) structure is similar for all our tables:
timestamp(Timestamp) ;…
JPelletier
- 593
- 1
- 5
- 6
41
votes
5 answers
Backup only one schema 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" (including data) on a different server. Not sure how to do this as I am new to Postgres.
Do I have to create a new database on the new sever as of…
user30422
- 511
- 1
- 4
- 3
40
votes
3 answers
Forgotten PostgreSQL Windows password
This morning I’ve been trying to connect the PostgreSQL database on my Windows 7 Professional desktop.
The default value is ‘postgres’, but sure enough I forgot what password I used when I originally installed it.
I have googled and found a post…
AKIWEB
- 585
- 2
- 5
- 8
38
votes
1 answer
restoring Postgres database: pg_restore -vs- just using psql
I am dumping my Postgres database using pg_dump (in plain-text format) and then restore it simply by using psql (with the -f option).
Which begs the question: am I missing anything by not using pg_restore which seems like a specialized restore tool…
Marcus Junius Brutus
- 3,119
- 7
- 28
- 41
34
votes
3 answers
How do I list all tables in all schemas owned by the current user in Postgresql?
I can list all tables in all schemas using
> \dt *.*
but that also lists system tables that greatly outnumber my tables that I care about. I'd like all the tables (and possibly views) created by me in the public schema and any schemas I've…
Peter Groves
- 1,035
- 2
- 9
- 7
34
votes
4 answers
Moving postgresql data to different drive
I am using AWS as my cloud environment. I installed PostgreSQL on the same drive as my root instance volume. I have attached and mounted the second drive to my instance. Now I want to move all my PostgreSQL data to the different drive. I am still in…
codecool
- 1,923
- 2
- 15
- 21
29
votes
4 answers
pg_restore: [archiver] did not find magic string in file header
I'm using PostgreSQL 9.1 and want to restore backup files generated with pg_dump:
sudo pg_dump -h 127.0.0.1 -U postgres --clean --inserts -E UTF8 -f out.sql database_name
This command generates a valid sql file that starts with droping any existing…
maja
- 393
- 1
- 4
- 7
28
votes
3 answers
upgrading from postgres 9.1 to 9.3 on ubuntu server
I have my production server(ubuntu 13.10) running with postgresql 9.1.
I want to use few features of 9.3, hence want to upgrade.
Could someone help me with upgrading from 9.1 to 9.3 so that there is a downtime of not more than 30 mins. or so?
Prime…
Ram Kumar
- 383
- 1
- 3
- 4