PostgreSQL – pgAdmin – path to database

pgadminpostgresql

I am new to PostgreSQL and I can not found info about this. I am using pgAdmin III and my databse files are kept in instalation directory of PostgreSQL. So far so good. But now, I need to create huge DB, that is out of my HDD capacity. So I create RAID and want to move this single DB to this location. How can I specify path for single database to be somewhere else?

I dont want other on the same disk, since I have them on fast SSDs. The big one will be only for some data mining and I will remove it afterwards.

Best Answer

Have a look a tablespaces. You can keep the main and small parts of the database in the SSD drives and migrate the bigger tables to the tablespaces in your other drive.

http://www.postgresql.org/docs/9.4/static/manage-ag-tablespaces.html

In alternative the whole database can be put into the tablespace by setting the default tablespace parameter.

Related Solutions

PostgreSQL Primary key disappears from test table

For a table created like this:

CREATE TABLE public.delete_key_bigserial (id bigserial PRIMARY KEY NOT NULL);

... both my queries in the previous answer (as well as pgAdmin, psql or any other decent client) would find the PK constraint. If it's not there, you removed it somehow.
Note that my first query only returns the column if it is the PK and a serial type - which is the case for the example.

Another possible cause for the confusion: Maybe you have more than one table named delete_key_bigserial in your database? Table names are only unique inside a single schema. Test with:

SELECT * FROM pg_class WHERE relname = 'delete_key_bigserial';

To make your query unambiguous, schema-qualify the table name:

WHERE  a.attrelid = 'public.delete_key_bigserial'::regclass

There are ways to make the constraint "disappear" without leaving a DROP CONSTRAINT in your logs.

Drop and recreate the table.
Drop and recreate the schema or database.
(Temporarily) set log_statement or other relevant settings so the statement is not logged.
Manipulate the system catalogs directly (as superuser) Internally, the primary key is set with contype = 'p' in the table pg_constraint.
Edit the log files.
etc.

Postgresql – Start, stop, configure PostgreSQL through Mac terminal

The first error indcates the server is already running. Have you tried connecting with psql or run the following to establish that?

pg_ctl -D /usr/local/cellar/postgresql/9.5.2/data status

If you know it is down, then for the second error with the logfile on startup, either reissue the command with an absolute path to where you can write a file:

pg_ctl -D /usr/local/cellar/postgresql/9.5.2/data -l /tmp/logfile start

Or if that doesn't work for you, try starting without the -l switch:

pg_ctl -D /usr/local/cellar/postgresql/9.5.2/data start

Without the switch, you still get the PostgreSQL logs in the pg_log directory under the $PGDATA - in your case /usr/local/cellar/postgresql/9.5.2/data/pg_log

Best Answer

Related Solutions

PostgreSQL Primary key disappears from test table

Postgresql – Start, stop, configure PostgreSQL through Mac terminal

Related Question