0

OK... looked through all of the Q&A's on PostgreSQL and PDO, but no luck answering my question... same problem finding the answer through Google.

Thus I lay my query at the feet of the masters.

I am just getting started with PostgreSQL, coming from a MySQL background. I am using PDO to connect to it, and pgAdminIII to manage the database itself.

How do I connect to a table which has been stuck in a defined tablespace?

$db = new PDO("pgsql:host=127.0.0.1;user=pageserver;password=myPassword;dbname=pageserver;");

is resulting in:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[08006] [7] FATAL: database "pageserver" does not exist' in ~stacktrace~

Before anyone mentions, sure, I should be using try/catch, and I will as this gets further along... but for now the error is helpful.

This database does exist, but all of the databases that i am working with on this project are placed in a tablespace to keep the databases within specific subfolders.

I am assuming this is where my problem is coming in; so far, I have seen no references on tablespaces anywhere through the PDO documentation.

The documentation for PostgreSQL obviously goes into great length on tablespaces, but doesn't reference PDO syntax for connecting to them.

I am hoping it is something simple like:

$db = new PDO("pgsql:host=127.0.0.1;tablespace=system;user=pageserver;password=MyPassword;dbname=pageserver;");

But this results in:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[08006] [7] invalid connection option "tablespace"' in ~stacktrace~

@Igor

Well, I just spent about half an hour running further tests, and exploring the structure, and ensuring that the roles, schemas, tablespaces, and tables were all in line...

They were all in order, and actually set up correctly.

I tried using schema.database name as you recommended, but it didn't work.

Then I discovered the problem that had been plaguing me this entire time.

Apparently, after having setting up the databases, then close to 40 tables, including all of the indexes... my problem wasn't even PostgreSQL.

It was an evil combination of PgAdminIII and my complete lack of sleep.

Apparently, all of these tables got set up under the db 'postgres' instead of under the db's they were meant to be placed within.

My apologies for my idiocy, and thank you for seeing it clearly.

Marked as correct.

Steve
  • 580
  • 7
  • 14
  • A bit offtopic but... Do you really count try..catch as a some sort of error suppression measure? – Your Common Sense Jul 26 '13 at 18:26
  • Do Postgres have it's own native way of selecting a database and tablespace, something like 'use database'? Did you try to omit dbname from DSN and then select it manually? – Your Common Sense Jul 26 '13 at 18:30
  • @CommonSense No, not error suppression, but error HANDLING. I use it for when something not-quite-unexpected occurs, and use it to hand it off to wherever it needs to go to be handled correctly. In this case, it will be used to further determine if the failure to connect to the database was a connection problem, for example. – Steve Jul 26 '13 at 19:16
  • Why can't you use one you have already? – Your Common Sense Jul 26 '13 at 19:20
  • @CommonSense2 Not that I know of, but as stated, I am new to PostgreSQL. I have been searching for 'postgresql connection string' and the like trying to find how to select a specific tablespace, but so far, no go. It was a requested 'feature'. The client imagines that backup and re-installation of the application on different servers would be simpler and less error prone due to the database being held in a specified folder right along with the rest of the site. Personally, I would rather dive in with the devil that I know... but the client is always right. – Steve Jul 26 '13 at 19:22
  • @Steve Did you move the whole postgres cluster to a specific folder or just created a separate tablespace in the folder? – Ihor Romanchenko Jul 26 '13 at 19:24
  • @CommonSense - The *which* that I have already? The connection string? The default tablespace? Not sure I really understand the question... – Steve Jul 26 '13 at 19:33
  • @Igor ~ The tablespaces hold individual databases... there are actually a total of three. So there is a 'data' folder, and within it are three subfolders, separated by schema, each with its own database. 'pageserver' is one of the schemas; it checks user permissions, then serves the requested data, if allowed. The database within this tablespace is similarly 'pageserver', and the tables within that are 'page' and 'permissions'. – Steve Jul 26 '13 at 19:37
  • @Steve You messed up logical and physical structure of a database. Schemas do not define a storage subfolder in any way, but tablespaces do. And tablespaces can not hold databases. Database cluster holds databases. And databases use **table** spaces to store **tables**. And in databesas tables are logically divided into schemas. – Ihor Romanchenko Jul 26 '13 at 19:52

1 Answers1

2

First of all: How do I connect to a table ... - you do not connect to a table. You connect to a database. When you are connected to a DB you can acces the table.

Second - tablespaces do not affect in any way the acces to the tables of DB. Tablespaces are only for organising physical storage of the tables. You may have mistaken tablespaces for schems. Schemas are used for logical organisatin of tables inside a DB.

So, to get data from a table in postgres you need:

Connect to the database, that has that table. If you cannot connect to the database, check if it exists, you are connecting to the right server and server is using the right database cluster.

When connected to DB you can select data from any table of this DB. If you need to select data from a table in particul schema of the DB - use the full name of the table -'schema_name.table_name'.

If you do not want to specify a full name of the table every time - you can modify search_path variable to set default schema.

For the second question - for bulk data loading you can use csv import via COPY command. For test data generation - there are pleanty of tools, both paid and open source, for that. You just need to search (or pay) for them.

The general structure of postgre databese.

First - there is a database cluster. It is a folder, that holds the config files, logs, default tablespace and main information about databases in this cluster.

When you start a postgres server you must specify one database cluster it will use.

Next there are databases. When you connect to a postgres server you must specify a database, that exists in the database cluster, the server is using.

Next there are schemas in the database. The default public schema and any number of user created schemas. Schemas are only used to logicaly divide your tables into some subsets.

Next there are tables. They belong to a particular schema of a particular DB. Tables store data in a tablespace.

A database can use any nubre of tablespaces. And a tablespace can be used by any number of databases. Tablespaces only define a folder, where the data of a table (that uses this tablespace) is store. Changes to the tablespaces do not affect the way you connect to a database or the way you select data from tables.

Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44
  • OK... perhaps I misspoke. Edited question with clarification, as adding the scheme to the table name did not help. – Steve Jul 26 '13 at 20:01
  • 1
    @Steve Added an explanation about postgres database cluster structure. – Ihor Romanchenko Jul 26 '13 at 20:05
  • Sorry, took me a while, ran some test code while rewriting to ensure my answer was accurate, and then found my error. – Steve Jul 26 '13 at 20:25
  • and thank you for your explanation. Seems clear explanation of these matters is sparse with pgSQL, it is quite welcome. – Steve Jul 26 '13 at 20:27
  • @Steve BTW, if you are familiar with phpMyAdmin you can use phpPgAdmin. Or any other tool from [`this list`](http://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools). For me the best one was EMS SQL Manager. – Ihor Romanchenko Jul 26 '13 at 20:36
  • Thank you.. I had checked several links, and everyone seemed to recommend Adminer.php, but it doesn't really fit my bill. PgAdminIII seems to work very well for many things, but dummy data doesn't seem to be one of its key features either. I will check the list. I find it fascinating that OpenOffice and its twin found themselves a spot. – Steve Jul 26 '13 at 21:10