In the logical structure, we can see different databases and tables or any other objects under it but as DBA we should also be aware of how to find out the location of data files associated with a database or a table.
In the article table creation in Postgres database we learned to create tables now we use the same tables here to perform a demonstration. Assume we have the below details
Database name- tech_db
Table name - employee
tech_db=# \c tech_db
You are now connected to database "tech_db" as user "postgres".
tech_db=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | employee | table | postgres
(1 row)
tech_db=# select * from employee;
emp_id | emp_name | age
--------+----------+-----
123 | sam | 25
124 | John | 24
(2 rows)
as of now we do not physical location of this database or table, now we will try to find out the same
Read More : Database Creation in PostgreSQL
Data Directory
First of all check data directory using show command
tech_db=# show data_directory;
data_directory
------------------
E:/postgres/data
(1 row)
Above we got data directory, as by default folders for each database gets created under data_directory/base location
as we see folder as created with some random numbers which is called as OID , value of OID can be find using below query.
tech_db=# select datname, oid from pg_database where datname='tech_db';
datname | oid
---------+-------
tech_db | 16393
(1 row)
we can see value of oid for database tech_db is 16393 and in above screenshot we can find the same folder under base directory. Under it you can find number of files and now lets find the file related to our table employee
tech_db=# select relname,oid, relfilenode from pg_class where relname='employee';
relname | oid | relfilenode
----------+-------+-------------
employee | 16394 | 16394
(1 row)
pick up the relfilenode from above output, you will be able to see same file under folder 16393
This is an exclusive post about PostgreSQL and the use of foreign data wrapper actually overcomes and helps people solve the most complex problems and errors.
ReplyDeleteSSIS postgresql read