Обсуждение: location for pg_default tablespace
I am new at Postgresql. Previously I used to work with Oracle.
I am surprised to see that the location for pg_default tablespace in my database for a Postgresql cluster is null.
Could anyone please tell me what is the location of default tablespace in postgresql and how could I find it?
AI Rumman wrote: > I am new at Postgresql. Previously I used to work with Oracle. > I am surprised to see that the location for pg_default tablespace in > my database for a Postgresql cluster is null. > Could anyone please tell me what is the location of default tablespace > in postgresql and how could I find it? Its the $PGDATA directory. The location of this varies widely by operating system and postgres distribution. For instance, the postgres supplied with fedora and redhat linuxes tend to put this in /var/lib/pgsql/data ... If you build postgres from source on a unix-like system, using full defaults, its probably /usr/local/pgsql/data
Thanks.
But actually I want to know that why the value in spclocation is null is pg_tablespace for pg_default.
Moreover, $PGDATA/pg_tblspc has no file.
Could you please tell me why?
On Tue, Jan 12, 2010 at 11:26 AM, John R Pierce <pierce@hogranch.com> wrote:
Its the $PGDATA directory. The location of this varies widely by operating system and postgres distribution. For instance, the postgres supplied with fedora and redhat linuxes tend to put this in /var/lib/pgsql/data ... If you build postgres from source on a unix-like system, using full defaults, its probably /usr/local/pgsql/dataAI Rumman wrote:I am new at Postgresql. Previously I used to work with Oracle.
I am surprised to see that the location for pg_default tablespace in my database for a Postgresql cluster is null.
Could anyone please tell me what is the location of default tablespace in postgresql and how could I find it?
AI Rumman wrote: > But actually I want to know that why the value in spclocation is null > is pg_tablespace for pg_default. > Moreover, $PGDATA/pg_tblspc has no file. > Could you please tell me why? PostgreSQL ships with a blank tablespace setting, which it interprets as meaning you want to put the database table files into the default tablespace which is stored in $PGDATA/base When you create a new tablespace using CREATE TABLESPACE: http://www.postgresql.org/docs/current/static/sql-createtablespace.html Then you'll find that pg_tblspc contains a symbolic link to the new directory you've told it to use. At that point, you can then point all new creation toward that location by setting default_tablespace, or put individual bits of data onto there with the appropriate options to CREATE: http://www.postgresql.org/docs/current/static/manage-ag-tablespaces.html -- Greg Smith 2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.com
I used the followings:
create tablespace mytabspc location '/var/lib/pgsql/data/pg_tblspc';
create database mydb with tablespace=mytabspc;
drop database mydb;
drop tablespace mytabspc;
ERROR: tablspace 'mytabspc' is not empty
Please tell me why?
On Tue, Jan 12, 2010 at 11:50 AM, Greg Smith <greg@2ndquadrant.com> wrote:
AI Rumman wrote:PostgreSQL ships with a blank tablespace setting, which it interprets as meaning you want to put the database table files into the default tablespace which is stored in $PGDATA/baseBut actually I want to know that why the value in spclocation is null is pg_tablespace for pg_default.
Moreover, $PGDATA/pg_tblspc has no file.
Could you please tell me why?
When you create a new tablespace using CREATE TABLESPACE: http://www.postgresql.org/docs/current/static/sql-createtablespace.html
Then you'll find that pg_tblspc contains a symbolic link to the new directory you've told it to use. At that point, you can then point all new creation toward that location by setting default_tablespace, or put individual bits of data onto there with the appropriate options to CREATE: http://www.postgresql.org/docs/current/static/manage-ag-tablespaces.html
--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.com
AI Rumman wrote: > I used the followings: > > create tablespace mytabspc location '/var/lib/pgsql/data/pg_tblspc'; > > create database mydb with tablespace=mytabspc; > > drop database mydb; > > drop tablespace mytabspc; > ERROR: tablspace 'mytabspc' is not empty > > Please tell me why? You don't put things in pg_tblspace yourself; that directory is for the database to manage. Your tablespace should be somewhere completely outside of /var/lib/pgsql/data altogether. -- Greg Smith 2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.com
On Tue, Jan 12, 2010 at 7:17 AM, Greg Smith <greg@2ndquadrant.com> wrote:
AI Rumman wrote:You don't put things in pg_tblspace yourself; that directory is for the database to manage. Your tablespace should be somewhere completely outside of /var/lib/pgsql/data altogether.I used the followings:
create tablespace mytabspc location '/var/lib/pgsql/data/pg_tblspc';
create database mydb with tablespace=mytabspc;
drop database mydb;
drop tablespace mytabspc;
ERROR: tablspace 'mytabspc' is not empty
Please tell me why?
Tablespaces in postgres are quite a bit different from Oracle. In Oracle, you define a tablespace to be a location that uses one or more datafiles, and everything belongs in a tablespace.
In postgres, a tablespace is not required. It is nothing more than a directory on some filesystem that you tell postgres about so that you can store relations in it. If you don't define a tablespace in postgres, all of your relations are going to be located in /var/lib/pgsql/base.
That's the difference, you don't NEED a tablespace like Oracle, you'll only use them in postgres when you're trying to store data in postgres on a different mountpoint.
Hope this helps.
--Scott