Posterous
Daniele is using Posterous to post everything online. Shouldn't you?
Unknown35
Daniele Pignedoli
owns this site
3
subscribers
 

Daniel’strae

Is cuma cá mhinice a théann tú ar strae; is é is tábhachtaí gurb áil leat do bhealach a aimsiú arís.

Memo: mysqldump quick usage

Dump ALL MySQL Databases:

mysqldump --user=XXXXXXXX --password=XXXXXXX -A > /PATH/TO/DUMPFILE.SQL

Dump SOME databases:
mysqldump --user=XXXXXXXX --password=XXXXXXX DB_NAME1 DB_NAME2 DB_NAME3 > /PATH/TO/DUMPFILE.SQL

Dump only certain tables from a MySQL Database:
mysqldump --user=XXXXXXXX --password=XXXXXXXX DB_NAME --tables TABLE_NAME > /PATH/TO/DUMPFILE.SQL

Restore a database:
mysql --user=XXXXXXXX --password=XXXXXXXX DB_NAME < /PATH/TO/DUMPFILE.SQL

note that the restore command run throught [mysql] command and not [mysqldump]

Loading mentions Retweet
Filed under  //   Database   mysql  
Posted September 17, 2009
// 0 Comments

Postgresql: duplicate schemas automatically

After this comment to a my question, im thinking if is better using 1 database with X schemas or viceversa.

My situation: im developing a web-app where, when people do register, i create (actually) a database (no, its not a social network: everyone must have access to his own data and never see the data of the other user).

Thats the way i used for the previus verison of my application (that is still running on mysql): throught the plesk api, for every registration i do:

  1. Create a database user with limited privileges;
  2. Create a database that can be accessed just by the previous created user and the superuser (for maintenance)
  3. Populate the db

Now, i'll need to do the same with posrtgresql (the project is getting mature and mysql.. dont fulfill all the needes)

I need to have all the databases/schemas backups indipendent: pg_dump works perfectly in both ways, the same for the users that can be configured to access just 1 schema or 1 database.

So, assuming you are more experienced potsgres users than me, what do you think is the best solution for my situation, and why?

There will be performance differences using $x db instead of $x schemas? And what solution will be better to mantein in future (reliability)?

Every help and suggestion is really appreciated.

Edit: i almost forgot: all of my databases/schemas will allways have the same structure!

Edit2: For the backups issue (using pg_dump), is maybe better using 1 db and many schemas, dumping all the schemas at once: recovering will be quite simple loading the main dump in a dev machine and then dump and restore just the schema needed: there is 1 additional step, but dumping all the schema seem faster then dumpin them one by one.

p.s: sorry if i forgot some 'W' char in the text, my keyboard suffer that button ;)

edit|close|delete|flag
 
 
"all of my databases/schemas will ever have the same structure!" do you mean they all have the same structure? Or never? – Osama ALASSIRY 17 hours ago
Sorry, yes, they all have the same structure forever: if i change one, i'll change all of them ;) – DaNieL 16 hours ago 

4 Answers

vote up 1 vote down
check

A PostgreSQL "schema" is roughly the same as a MySQL "database". Having many databases on a PostgreSQL installation can get problematic; having many schemas will work with no trouble. So you definitely want to go with one database and multiple schemas within that database.

link

1
 
This. Postgres doesn't allow you to query across databases, which can be pretty annoying. – matt b 3 hours ago

Great, i havent thought the possibility to query another schema without changing database or changing the connection. that's all, i'll go for 1 db and many schemas. Thanks guys! – DaNieL 25 mins ago 
add comment

vote up 0 vote down
check

I would say, go with multiple databases AND multiple schemas :)

Schemas in postgres are a lot like packages in Oracle, in case you are familiar with those. Databases are meant to differentiate between entire sets of data, while schemas are more like data entities.

For instance, you could have one database for an entire application with the schemas "UserManagement", "LongTermStorage" and so on. "UserManagement" would then contain the "User" table, as well as all stored procedures, triggers, sequences etc. that are needed for the user management.

Databases are entire programs, schemas are components.

link|flag


... and so i'll have 1 database, with inside the schemas: $customer1_user_schema, $customer2_user_schema, $customer3_user_schema, $customer1_documents_schema, $customer2_documents_schema, $customer3_documents_schema? Mh... dont seem a reliable way... and what about performance? And what about the code of my application (will be php and python)? so many schemas.. – DaNieL 15 hours ago 
add comment

vote up 0 vote down
check

A number of schemas should be more lightweight than a number of databases, although I cannot find a reference which confirms this.

But if you really want to keep things very separate (instead of refactoring the web application so that a "costomer" column is added to your tables), you may still want to use separate databases: I assert that you can more easily make restores of a particular customer's database this way -- without disturbing the other customers.

link|flag

add comment

vote up 0 vote down
check

Definitely, i'll go for the 1-db-many-schemas approach. This let me to dump all the database but restore just 1 very easly, in many ways:

  1. Dump the db (all the schema), load the dump in a new db, dump just the schema i need, and restore back in main db
  2. Dump the schema separately, one by one (but i think the machine ill suffer more this way.. and im aspecting like 500 schemas!)

Otherwise, googlin around iv'se seen that there is no auto-procedure to duplicate a schema (using one as a template), but many suggest this way:

  1. Create a template-schema
  2. When need to duplicate, rename it with new name
  3. Dump it
  4. Rename it back
  5. Restore the dump
  6. The magis is done.

I've written 2 rows in python to do that, hope thay can help someone (in-2-seconds-written-code, dont use it in production):

import os
import sys
import pg

#Take the ne shcema name from the second cmd arguments (the first is the filename)

newSchema = sys.argv[1]

#Temp folder for the dumps
dumpFile = '/test/dumps/' + str(newSchema) + '.sql'

#Settings
db_name = 'db_name'
db_user = 'db_user'

db_pass = 'db_pass'
schema_as_template = 'schema_name'


#Connection
pgConnect = pg.connect(dbname= db_name, host='localhost', user= db_user, passwd= db_pass)

#Rename schema with the new name
pgConnect.query("ALTER SCHEMA " + schema_as_template + " RENAME TO " + str(newSchema))

#Dump it
command = 'export PGPASSWORD="' + db_pass + '" && pg_dump -U ' + db_user + ' -n ' + str(newSchema) + ' ' + db_name + ' > ' + dumpFile

os.system(command)
#Rename back with its default name

pgConnect.query("ALTER SCHEMA " + str(newSchema) + " RENAME TO " + schema_as_template)

#Restore the previus dump to create the new schema
restore = 'export PGPASSWORD="' + db_pass + '" && psql -U ' + db_user + ' -d ' + db_name + ' < ' + dumpFile

os.system(restore)
#Want to delete the dump file?

os.remove(dumpFile)
#Close connection

pgConnect.close()

Loading mentions Retweet
Filed under  //   Database   Postgresql   Python  
Posted July 20, 2009
// 0 Comments

Postgresql Explainer of Explain plan (ma LOL che figata)

http://explain.depesz.com/
Copy'n'paste your query plan, it will highligth all the 'bad steps'.

Dont know how does this works withour knowing the database structure, data-types and indexes, but.. its not bad

Loading mentions Retweet
Filed under  //   Database   Postgresql  
Posted June 22, 2009
// 0 Comments